关于Oracle的序列，相信大家并不陌生，但很多人平时只用到connect by 的方式来构造序列，今天一起来学习更多的构造序列的方法及每个方法的优缺点。
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> select count(*) from (select rownum rn from xmltable(‘1 to 67108864’));
- COUNT(*)
- ———-
- 67108864
- 已用时间: 00: 00: 37.00
这时间和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
