共计 6223 个字符，预计需要花费 16 分钟才能阅读完成。
本篇文章给大家分享的是有关 Oracle 中怎么构造序列，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。
Oracle 构造序列的方法随着版本一直在变化。在 9i 之前的版本，常用的方法是：
select rownum rn from all_objects where rownum从 all_objects 等系统视图中去获取序列的方式，虽然简单，但有一个致命的弱点是该视图的 sql 非常复杂，嵌套层数很多，一旦应用到真实案例中，极有可能碰到 Oracle 自身的 bug，所以这种方式不考虑，直接 pass 掉。
2、9i 之后，我们用 connect by
select rownum rn from dual connect by rownum3、自从 10g 开始支持 XML 后，还可以使用以下方式：
select rownum rn from xmltable(lsquo;1 to xx rsquo;);接下来我们从序列大小，构造时间等方面对比分析这两种方式。
1、先看 connect by 的方法
lastwinner@lw select count(*) from (select rownum rn from dual connect by rownum=power(2,19)); COUNT(*) mdash; mdash; mdash;- 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( lsquo;1 to 1048576 rsquo;)); COUNT(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 1073741824 已用时间: 00: 01: 07.37
耗时高达 1 分钟还多，再看看 xmltable 方式，考虑到 1M 的时候耗时就达到 0.95 秒，因此这里只测试 1 /16*1G，即 64M 的情况
lastwinner@lw select count(*) from (select rownum rn from xmltable( lsquo;1 to 67108864 rsquo;)); COUNT(*) mdash; mdash; mdash;- 67108864 已用时间: 00: 00: 37.00如果直接构造到 1G，那么时间差不多是 16*37s 这个级别。
但如果通过笛卡尔积 +xmltable 的方式来构造。
lastwinner@lw select count(*) from (select rownum rn from xmltable( lsquo;1 to 67108864 rsquo;)); COUNT(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 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 ndash; 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 67108864 Elapsed: 00:00:06.32以上就是 Oracle 中怎么构造序列，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。
