中国领先的IT技术网站
|
|

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

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

作者:怀晓明来源:运维派|2017-07-10 09:21

开发者大赛路演 | 12月16日,技术创新,北京不见不散


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

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

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

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

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

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

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

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

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

1、先看connect by的方法

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

再来64M的

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

这个速度并不快,但已经比直接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.  
  3. 2  c as (select rownum r from b,b,b,b,b), 
  4.  
  5. 3  d as (select rownum r from c,c,c,c,c,b) 
  6.  
  7. 4  select count(*) from d; 
  8.  
  9. COUNT(*) 
  10.  
  11. ———- 
  12.  
  13. 67108864 
  14.  
  15. 已用时间:  00: 00: 04.53 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

【编辑推荐】

  1. 从Oracle到MariaDB的同步复制
  2. SQL Server、Mysql、Oracle三种数据库的优缺点总结
  3. Oracle中,通过触发器,记录每个语句影响总行数
  4. 7 月全球数据库排名发布:Oracle扳回一局,MySQL最受欢迎
  5. Oracle使用联机重定义来给表增加新列与分区
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

嬴在用户:Web人物角色创建和应用实践指南

您如何保证您的网站确实给予用户他们所需要的,并对您产生商业成果?您需要了解谁是您的用户,您的用户的目标、行为和观点是什么,还要把他...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊