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

关于Oracle的序列，相信大家并不陌生，但很多人平时只用到connect by 的方式来构造序列，今天一起来学习更多的构造序列的方法及每个方法的优缺点。

创新互联公司坚持“要么做到，要么别承诺”的工作理念，服务领域包括：网站制作、成都网站建设、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的汤阴网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

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

 
 
 
      
  
  
  1. select rownum rn from all_objects where rownum<=xx; 
    2.

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

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

 
 
 
      
  
  
  1. select rownum rn from dual connect by rownum<=xx; 
    2.

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

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

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

1、先看connect by的方法

 
 
 
      
  
  
  1. lastwinner@lw> select count(*) from (select rownum rn from dual connect by rownum<=power(2,19)); 
    2.   
  
  
  2.  
    3.   
  
  
  3. COUNT(*) 
    4.   
  
  
  4.  
    5.   
  
  
  5. ———- 
    6.   
  
  
  6.  
    7.   
  
  
  7. 524288 
    8.   
  
  
  8.  
    9.   
  
  
  9. 已用时间: 00: 00: 00.20 
    10.   
  
  
  10.  
    11.   
  
  
  11. lastwinner@lw> select count(*) from (select rownum rn from dual connect by rownum<=power(2,20)); 
    12.   
  
  
  12.  
    13.   
  
  
  13. select count(*) from (select rownum rn from dual connect by rownum<=power(2,20)) 
    14.   
  
  
  14.  
    15.   
  
  
  15. * 
    16.   
  
  
  16.  
    17.   
  
  
  17. 第 1 行出现错误: 
    18.   
  
  
  18.  
    19.   
  
  
  19. ORA-30009: CONNECT BY 操作内存不足 
    20.

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

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

 
 
 
      
  
  
  1. lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 1048576’)); 
    2.   
  
  
  2.  
    3.   
  
  
  3. COUNT(*) 
    4.   
  
  
  4.  
    5.   
  
  
  5. ———- 
    6.   
  
  
  6.  
    7.   
  
  
  7. 1048576 
    8.   
  
  
  8.  
    9.   
  
  
  9. 已用时间: 00: 00: 00.95 
    10.

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

 
 
 
      
  
  
  1. lastwinner@lw> with a as (select rownum rn from dual connect by rownum<=power(2,10)) 
    2.   
  
  
  2.  
    3.   
  
  
  3. 2 select count(*) from (select rownum rn from a, a); 
    4.   
  
  
  4.  
    5.   
  
  
  5. COUNT(*) 
    6.   
  
  
  6.  
    7.   
  
  
  7. ———- 
    8.   
  
  
  8.  
    9.   
  
  
  9. 1048576 
    10.   
  
  
  10.  
    11.   
  
  
  11. 已用时间: 00: 00: 00.09 
    12.

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

 
 
 
      
  
  
  1. lastwinner@lw> with a as (select rownum rn from dual connect by rownum<=power(2,10)) 
    2.   
  
  
  2.  
    3.   
  
  
  3. 2 select count(*) from (select rownum rn from a, a, a); 
    4.   
  
  
  4.  
    5.   
  
  
  5. COUNT(*) 
    6.   
  
  
  6.  
    7.   
  
  
  7. ———- 
    8.   
  
  
  8.  
    9.   
  
  
  9. 1073741824 
    10.   
  
  
  10.  
    11.   
  
  
  11. 已用时间: 00: 01: 07.37 
    12.

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

 
 
 
      
  
  
  1. lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 67108864’)); 
    2.   
  
  
  2.  
    3.   
  
  
  3. COUNT(*) 
    4.   
  
  
  4.  
    5.   
  
  
  5. ———- 
    6.   
  
  
  6.  
    7.   
  
  
  7. 67108864 
    8.   
  
  
  8.  
    9.   
  
  
  9. 已用时间: 00: 00: 37.00 
    10.

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

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

 
 
 
      
  
  
  1. lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 67108864’)); 
    2.   
  
  
  2.  
    3.   
  
  
  3. COUNT(*) 
    4.   
  
  
  4.  
    5.   
  
  
  5. ———- 
    6.   
  
  
  6.  
    7.   
  
  
  7. 67108864 
    8.   
  
  
  8.  
    9.   
  
  
  9. 已用时间:  00: 00: 37.00 
    10.

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

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

 
 
 
      
  
  
  1. lastwinner@lw> with b as (select 1 r from dual union all select 2 from dual), 
    2.   
  
  
  2.  
    3.   
  
  
  3. 2  c as (select rownum r from b,b,b,b,b, 
    4.   
  
  
  4.  
    5.   
  
  
  5. 3  b,b,b,b,b, 
    6.   
  
  
  6.  
    7.   
  
  
  7. 4  b,b,b,b,b, 
    8.   
  
  
  8.  
    9.   
  
  
  9. 5  b,b,b,b,b) 
    10.   
  
  
  10.  
    11.   
  
  
  11. 6  select count(*) from c; 
    12.   
  
  
  12.  
    13.   
  
  
  13. COUNT(*) 
    14.   
  
  
  14.  
    15.   
  
  
  15. ———- 
    16.   
  
  
  16.  
    17.   
  
  
  17. 1048576 
    18.   
  
  
  18.  
    19.   
  
  
  19. 已用时间:  00: 00: 00.33 
    20.

再来64M的

 
 
 
      
  
  
  1. lastwinner@lw> ed 
    2.   
  
  
  2.  
    3.   
  
  
  3. 已写入 file afiedt.buf 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1  with b as (select 1 r from dual union all select 2 from dual), 
    6.   
  
  
  6.  
    7.   
  
  
  7. 2  c as (select rownum r from b,b,b,b,b, 
    8.   
  
  
  8.  
    9.   
  
  
  9. 3  b,b,b,b,b, 
    10.   
  
  
  10.  
    11.   
  
  
  11. 4  b,b,b,b,b, 
    12.   
  
  
  12.  
    13.   
  
  
  13. 5  b,b,b,b,b, 
    14.   
  
  
  14.  
    15.   
  
  
  15. 6  b,b,b,b,b,b) 
    16.   
  
  
  16.  
    17.   
  
  
  17. 7* select count(*) from c 
    18.   
  
  
  18.  
    19.   
  
  
  19. lastwinner@lw> / 
    20.   
  
  
  20.  
    21.   
  
  
  21. COUNT(*) 
    22.   
  
  
  22.  
    23.   
  
  
  23. ———- 
    24.   
  
  
  24.  
    25.   
  
  
  25. 67108864 
    26.   
  
  
  26.  
    27.   
  
  
  27. 已用时间:  00: 00: 16.62 
    28.

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

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

 
 
 
      
  
  
  1. lastwinner@lw> with b as (select 1 r from dual union all select 2 from dual), 
    2.   
  
  
  2.  
    3.   
  
  
  3. 2  c as (select rownum r from b,b,b,b,b), 
    4.   
  
  
  4.  
    5.   
  
  
  5. 3  d as (select rownum r from c,c,c,c,c,b) 
    6.   
  
  
  6.  
    7.   
  
  
  7. 4  select count(*) from d; 
    8.   
  
  
  8.  
    9.   
  
  
  9. COUNT(*) 
    10.   
  
  
  10.  
    11.   
  
  
  11. ———- 
    12.   
  
  
  12.  
    13.   
  
  
  13. 67108864 
    14.   
  
  
  14.  
    15.   
  
  
  15. 已用时间:  00: 00: 04.53 
    16.

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

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

 
 
 
      
  
  
  1. lastwinner@lw> ed 
    2.   
  
  
  2.  
    3.   
  
  
  3. 已写入 file afiedt.buf 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1  with b as (select 1 r from dual union all select 2 from dual), 
    6.   
  
  
  6.  
    7.   
  
  
  7. 2  c as (select rownum r from b,b,b,b,b), 
    8.   
  
  
  8.  
    9.   
  
  
  9. 3  d as (select rownum r from c,c,c,c,c,c) 
    10.   
  
  
  10.  
    11.   
  
  
  11. 4* select count(*) from d 
    12.   
  
  
  12.  
    13.   
  
  
  13. lastwinner@lw> / 
    14.   
  
  
  14.  
    15.   
  
  
  15. COUNT(*) 
    16.   
  
  
  16.  
    17.   
  
  
  17. ———- 
    18.   
  
  
  18.  
    19.   
  
  
  19. 1073741824 
    20.   
  
  
  20.  
    21.   
  
  
  21. 已用时间:  00: 01: 11.48 
    22.

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

 
 
 
      
  
  
  1. lastwinner@lw> ed 
    2.   
  
  
  2.  
    3.   
  
  
  3. 已写入 file afiedt.buf 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1  with b as (select 1 r from dual union all select 2 from dual), 
    6.   
  
  
  6.  
    7.   
  
  
  7. 2  c as (select rownum r from b,b,b), 
    8.   
  
  
  8.  
    9.   
  
  
  9. 3  d as (select rownum r from c,c,c), 
    10.   
  
  
  10.  
    11.   
  
  
  11. 4  e as (select rownum r from d,d,d,c) 
    12.   
  
  
  12.  
    13.   
  
  
  13. 5* select count(*) from e 
    14.   
  
  
  14.  
    15.   
  
  
  15. lastwinner@lw> / 
    16.   
  
  
  16.  
    17.   
  
  
  17. COUNT(*) 
    18.   
  
  
  18.  
    19.   
  
  
  19. ———- 
    20.   
  
  
  20.  
    21.   
  
  
  21. 1073741824 
    22.   
  
  
  22.  
    23.   
  
  
  23. 已用时间:  00: 01: 06.89 
    24.

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

 
 
 
      
  
  
  1. lastwinner@lw> ed 
    2.   
  
  
  2.  
    3.   
  
  
  3. 已写入 file afiedt.buf 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1  with b as (select 1 r from dual union all select 2 from dual), 
    6.   
  
  
  6.  
    7.   
  
  
  7. 2  c as (select rownum r from b,b,b,b,b), 
    8.   
  
  
  8.  
    9.   
  
  
  9. 3  d as (select rownum r from c,c), 
    10.   
  
  
  10.  
    11.   
  
  
  11. 4  e as (select rownum r from d,d,d) 
    12.   
  
  
  12.  
    13.   
  
  
  13. 5* select count(*) from e 
    14.   
  
  
  14.  
    15.   
  
  
  15. lastwinner@lw> / 
    16.   
  
  
  16.  
    17.   
  
  
  17. COUNT(*) 
    18.   
  
  
  18.  
    19.   
  
  
  19. ———- 
    20.   
  
  
  20.  
    21.   
  
  
  21. 1073741824 
    22.   
  
  
  22.  
    23.   
  
  
  23. 已用时间:  00: 01: 05.21 
    24.

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

 
 
 
      
  
  
  1. lastwinner@lw> ed 
    2.   
  
  
  2.  
    3.   
  
  
  3. 已写入 file afiedt.buf 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1  with b as (select rownum r from dual connect by rownum<=power(2,5)), 
    6.   
  
  
  6.  
    7.   
  
  
  7. 2  c as (select rownum r from b,b), 
    8.   
  
  
  8.  
    9.   
  
  
  9. 3  d as (select rownum r from c,c,c) 
    10.   
  
  
  10.  
    11.   
  
  
  11. 4* select count(*) from d 
    12.   
  
  
  12.  
    13.   
  
  
  13. lastwinner@lw> / 
    14.   
  
  
  14.  
    15.   
  
  
  15. COUNT(*) 
    16.   
  
  
  16.  
    17.   
  
  
  17. ———- 
    18.   
  
  
  18.  
    19.   
  
  
  19. 1073741824 
    20.   
  
  
  20.  
    21.   
  
  
  21. 已用时间:  00: 01: 05.07 
    22.

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

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

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

 
 
 
      
  
  
  1. lastwinner@lw> ed 
    2.   
  
  
  2.  
    3.   
  
  
  3. 已写入 file afiedt.buf 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1  with b as (select 1 r from dual union all select 2 from dual), 
    6.   
  
  
  6.  
    7.   
  
  
  7. 2  c as (select rownum r from b,b,b,b,b, 
    8.   
  
  
  8.  
    9.   
  
  
  9. 3  b,b,b,b,b, 
    10.   
  
  
  10.  
    11.   
  
  
  11. 4  b,b,b,b,b, 
    12.   
  
  
  12.  
    13.   
  
  
  13. 5  b,b,b,b,b, 
    14.   
  
  
  14.  
    15.   
  
  
  15. 6  b,b,b,b,b,b) 
    16.   
  
  
  16.  
    17.   
  
  
  17. 7* select count(*) from c 
    18.   
  
  
  18.  
    19.   
  
  
  19. lastwinner@lw> / 
    20.   
  
  
  20.  
    21.   
  
  
  21. COUNT(*) 
    22.   
  
  
  22.  
    23.   
  
  
  23. ———- 
    24.   
  
  
  24.  
    25.   
  
  
  25. 67108864 
    26.   
  
  
  26.  
    27.   
  
  
  27. 已用时间:  00: 00: 16.62 
    28.

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

 
 
 
      
  
  
  1. lastwinner@lw> ed 
    2.   
  
  
  2.  
    3.   
  
  
  3. 已写入 file afiedt.buf 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1  with b as (select 1 r from dual union all select 2 from dual), 
    6.   
  
  
  6.  
    7.   
  
  
  7. 2  c as (select rownum r from b,b,b,b), 
    8.   
  
  
  8.  
    9.   
  
  
  9. 3  d as (select rownum r from c,c,c), 
    10.   
  
  
  10.  
    11.   
  
  
  11. 4  e as (select rownum r from d,d,b,b) 
    12.   
  
  
  12.  
    13.   
  
  
  13. 5* select count(*) from e 
    14.   
  
  
  14.  
    15.   
  
  
  15. lastwinner@lw> / 
    16.   
  
  
  16.  
    17.   
  
  
  17. COUNT(*) 
    18.   
  
  
  18.  
    19.   
  
  
  19. ———- 
    20.   
  
  
  20.  
    21.   
  
  
  21. 67108864 
    22.   
  
  
  22.  
    23.   
  
  
  23. 已用时间:  00: 00: 04.00 
    24.

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

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

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

 
 
 
      
  
  
  1. create or replace function generator (n pls_integer) return sys.odcinumberlist pipelined is 
    2.   
  
  
  2. m pls_integer := trunc(n / 10); 
    3.   
  
  
  3. r pls_integer := n – 10 * m; 
    4.   
  
  
  4. begin 
    5.   
  
  
  5. for i in 1 .. m loop 
    6.   
  
  
  6. pipe row (null); 
    7.   
  
  
  7. pipe row (null); 
    8.   
  
  
  8. pipe row (null); 
    9.   
  
  
  9. pipe row (null); 
    10.   
  
  
  10. pipe row (null); 
    11.   
  
  
  11. pipe row (null); 
    12.   
  
  
  12. pipe row (null); 
    13.   
  
  
  13. pipe row (null); 
    14.   
  
  
  14. pipe row (null); 
    15.   
  
  
  15. pipe row (null); 
    16.   
  
  
  16. end loop; 
    17.   
  
  
  17. for i in 1 .. r loop 
    18.   
  
  
  18. pipe row (null); 
    19.   
  
  
  19. end loop; 
    20.   
  
  
  20. end; 
    21.   
  
  
  21. / 
    22.   
  
  
  22. alter function generator compile plsql_code_type = native; 
    23.   
  
  
  23.  
    24.   
  
  
  24. SQL> select count(*) from table(generator(67108864)); 
    25.   
  
  
  25.  
    26.   
  
  
  26. COUNT(*) 
    27.   
  
  
  27. ———- 
    28.   
  
  
  28. 67108864 
    29.   
  
  
  29.  
    30.   
  
  
  30. Elapsed: 00:00:06.68 
    31.   
  
  
  31.  
    32.   
  
  
  32. SQL> with b as (select 1 r from dual union all select 2 from dual), 
    33.   
  
  
  33. 2  c as (select rownum r from b,b,b,b), 
    34.   
  
  
  34. 3  d as (select rownum r from c,c,c), 
    35.   
  
  
  35. 4  e as (select rownum r from d,d,b,b) 
    36.   
  
  
  36. 5  select count(*) from e; 
    37.   
  
  
  37.  
    38.   
  
  
  38. COUNT(*) 
    39.   
  
  
  39. ———- 
    40.   
  
  
  40. 67108864 
    41.   
  
  
  41.  
    42.   
  
  
  42. Elapsed: 00:00:06.32 
    43.

当前名称：Oracle构造序列的方法分析对比
链接地址：http://www.shufengxianlan.com/qtweb/news24/70274.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

定制开发知识

同城分类信息

成都门户网站建设　　 　广安园林绿化公司　　 　服务器托管　　 　成都移动主机托管　　 　崇州做网站　　 　成都网站建设　　 　成都娃娃鱼　　 　做网站　　 　手机软件开发　　 　小谭网创广告　　 　宜宾网站建设　　 　不锈钢防护栏　　 　微信营销　　 　电商网店　　 　证企宝企业服务　　 　成都木托盘　　 　Android开发　　 　西云机房　　 　四川国泰民安消防　　 　成都做手机网站