欢迎您光临本站,如有问题请及时联系我们。

Oracle构造序列的方法分析对比

  Oracle构造序列的方法随着版本一直在变化。在9i之前的版本,常用的方法是:

  select rownum rn from all_objects where rownum<=xx;

  从all_objects等系统视图中去获取序列的方式,虽然简单,但有一个致命的弱点是该视图的sql非常复杂,嵌套层数很多,一旦应用到真实案例中,极有可能碰到Oracle自身的bug,所以这种方式不考虑,直接pass掉。

  2、9i之后,我们用connect by

  select rownum rn from dual connect by rownum<=xx;

  3、自从10g开始支持XML后,还可以使用以下方式:

  select rownum rn from xmltable(‘1 to xx’);

  接下来我们从序列大小,构造时间等方面对比分析这两种方式。

  1、先看connect by的方法

  lastwinner@lw> select count(*) from (select rownum rn from dual connect by rownum<=power(2,19));

  COUNT(*)

  ———-

  524288

  已用时间: 00: 00: 00.20

  lastwinner@lw> select count(*) from (select rownum rn from dual connect by rownum<=power(2,20));

  select count(*) from (select rownum rn from dual connect by rownum<=power(2,20))

  *

  第 1 行出现错误:

  ORA-30009: CONNECT BY 操作内存不足

  可见直接用connect by去构造较大的序列时,消耗的资源很多,速度也快不到哪儿去。实际上2^20并不是一个很大的数字,就是1M而已。

  但xmltable方式就不会耗这么多资源

  lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 1048576’));

  COUNT(*)

  ———-

  1048576

  已用时间: 00: 00: 00.95

  其实除了上述三种办法,我们还可以使用笛卡尔积来构造序列。如果换成笛卡尔连接的方式,那么构造2^20时,connect by也ok

  lastwinner@lw> with a as (select rownum rn from dual connect by rownum<=power(2,10))

  2 select count(*) from (select rownum rn from a, a);

  COUNT(*)

  ———-

  1048576

  已用时间: 00: 00: 00.09

  我们试着将1M加大到1G,在connect by方式下

  lastwinner@lw> with a as (select rownum rn from dual connect by rownum<=power(2,10))

  2 select count(*) from (select rownum rn from a, a, a);

  COUNT(*)

  ———-

  1073741824

  已用时间: 00: 01: 07.37

  耗时高达1分钟还多,再看看xmltable方式,考虑到1M的时候耗时就达到0.95秒,因此这里只测试1/16*1G,即64M的情况

  lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 67108864’));

  COUNT(*)

  ———-

  67108864

  已用时间: 00: 00: 37.00

  如果直接构造到1G,那么时间差不多是16*37s这个级别。

  但如果通过笛卡尔积+xmltable的方式来构造。

  lastwinner@lw> with a as (select rownum rn from xmltable(‘1 to 1024’))

  2 select count(*) from (select rownum rn from a, a, a);

  COUNT(*)

  ———-

  1073741824

  已用时间: 00: 01: 07.95

  这时间和connect by的差不多。以上测试,总的可见,在构造较大序列时,笛卡尔积的方式是最佳的,单纯使用connect by会遭遇内存不足,而单独使用xmltable则会耗费较多的时间。

  现在再看看基本用纯表连接的方式来构造同样大小的序列,先来1M的

  lastwinner@lw> with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b,b,b,

  3 b,b,b,b,b,

  4 b,b,b,b,b,

  5 b,b,b,b,b)

  6 select count(*) from c;

  COUNT(*)

  ———-

  1048576

  已用时间: 00: 00: 00.33

  再来64M的

  lastwinner@lw> ed

  已写入 file afiedt.buf

  1 with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b,b,b,

  3 b,b,b,b,b,

  4 b,b,b,b,b,

  5 b,b,b,b,b,

  6 b,b,b,b,b,b)

  7* select count(*) from c

  lastwinner@lw> /

  COUNT(*)

  ———-

  67108864

  已用时间: 00: 00: 16.62

  这个速度并不快,但已经比直接xmltable快了。

  其实64M,即64*2^20可以表示为(2^5)^5*2,那我们来改写一下64M的sql

  lastwinner@lw> with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b,b,b),

  3 d as (select rownum r from c,c,c,c,c,b)

  4 select count(*) from d;

  COUNT(*)

  ———-

  67108864

  已用时间: 00: 00: 04.53

  可以看到,从16s到4s,已经快了很多。这个示例告诉我们,中间表c 在提高速度方面起到了很好的作用。

  但在构造到1G时,还是要慢一些

  lastwinner@lw> ed

  已写入 file afiedt.buf

  1 with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b,b,b),

  3 d as (select rownum r from c,c,c,c,c,c)

  4* select count(*) from d

  lastwinner@lw> /

  COUNT(*)

  ———-

  1073741824

  已用时间: 00: 01: 11.48

  尝试相对较快的写法,多一层中间表

  lastwinner@lw> ed

  已写入 file afiedt.buf

  1 with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b),

  3 d as (select rownum r from c,c,c),

  4 e as (select rownum r from d,d,d,c)

  5* select count(*) from e

  lastwinner@lw> /

  COUNT(*)

  ———-

  1073741824

  已用时间: 00: 01: 06.89

  更快一点(思路,32^2=1024, 1G=2^30=(2^5)^6=((2^5)^2)^3 。)

  lastwinner@lw> ed

  已写入 file afiedt.buf

  1 with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b,b,b),

  3 d as (select rownum r from c,c),

  4 e as (select rownum r from d,d,d)

  5* select count(*) from e

  lastwinner@lw> /

  COUNT(*)

  ———-

  1073741824

  已用时间: 00: 01: 05.21

  这时候我们将2^5=32换成直接构造出来的方式

  lastwinner@lw> ed

  已写入 file afiedt.buf

  1 with b as (select rownum r from dual connect by rownum<=power(2,5)),

  2 c as (select rownum r from b,b),

  3 d as (select rownum r from c,c,c)

  4* select count(*) from d

  lastwinner@lw> /

  COUNT(*)

  ———-

  1073741824

  已用时间: 00: 01: 05.07

  可见所耗费的时间差不多。

  由此我们还可以得出,表连接的代价其实也是昂贵的,适当的减少表连接的次数,适当的使用with里的中间表,能有效提高系统性能。

  再重复一下刚才构造64M(2^26)的场景

  lastwinner@lw> ed

  已写入 file afiedt.buf

  1 with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b,b,b,

  3 b,b,b,b,b,

  4 b,b,b,b,b,

  5 b,b,b,b,b,

  6 b,b,b,b,b,b)

  7* select count(*) from c

  lastwinner@lw> /

  COUNT(*)

  ———-

  67108864

  已用时间: 00: 00: 16.62

  总共25次的表连接,1层嵌套,让速度非常慢。提高一下(26=4*3*2+2*2),总共8次表连接,3层嵌套。

  lastwinner@lw> ed

  已写入 file afiedt.buf

  1 with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b,b),

  3 d as (select rownum r from c,c,c),

  4 e as (select rownum r from d,d,b,b)

  5* select count(*) from e

  lastwinner@lw> /

  COUNT(*)

  ———-

  67108864

  已用时间: 00: 00: 04.00

  效率提升4倍。要注意在这个案例中并非表连接越少越好,嵌套层数也是需要关注的指标。执行计划有兴趣的同学自己去看吧,我就不列了,上例中,系统生成的中间表有3个。

  最终结论,构造较大序列时,例如同样是构造出64M的序列,oracle在处理时,用表连接的方式明显占优。但考虑到书写的便利性,因此在构造较小序列的时候,比如不超过1K的序列,那么直接用connect by或xmltable的方式就好了。

  附:newkid 回复方法,表示更灵活,有兴趣的同学可以尝试:

  create or replace function generator (n pls_integer) return sys.odcinumberlist pipelined is

  m pls_integer := trunc(n / 10);

  r pls_integer := n – 10 * m;

  begin

  for i in 1 .. m loop

  pipe row (null);

  pipe row (null);

  pipe row (null);

  pipe row (null);

  pipe row (null);

  pipe row (null);

  pipe row (null);

  pipe row (null);

  pipe row (null);

  pipe row (null);

  end loop;

  for i in 1 .. r loop

  pipe row (null);

  end loop;

  end;

  /

  alter function generator compile plsql_code_type = native;

  SQL> select count(*) from table(generator(67108864));

  COUNT(*)

  ———-

  67108864

  Elapsed: 00:00:06.68

  SQL> with b as (select 1 r from dual union all select 2 from dual),

  2 c as (select rownum r from b,b,b,b),

  3 d as (select rownum r from c,c,c),

  4 e as (select rownum r from d,d,b,b)

  5 select count(*) from e;

  COUNT(*)

  ———-

  67108864

  Elapsed: 00:00:06.32


来源:本文由E8运维原创撰写,欢迎分享本文,转载请保留出处和链接!