查看: 7307|回复: 30

[原创] 几种构造序列的方法比较

[复制链接]
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2013-6-14 15:13 | 显示全部楼层 |阅读模式
构造序列,很久以前,我们会用
  1. select rownum rn from all_objects where rownum<=xx;
复制代码
9i之后,我们用connect by
  1. select rownum rn from dual connect by rownum<=xx;
复制代码
自从10g开始支持XML后
  1. select rownum rn from xmltable('1 to xx');
复制代码


以上,xx为具体的数值。
现在,我们就来看看几种不同方法之间的效率差别。

招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2013-6-14 15:15 | 显示全部楼层
从all_objects等系统视图中去获取序列的方式,我并不赞同,虽然那样的方法简单,但有一个致命的弱点是该视图的sql非常复杂,嵌套层数很多,一旦应用到真实案例中,极有可能碰到Oracle自身的bug,所以这种方式不考虑,直接pass掉。

先看connect by的方法

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

  2.   COUNT(*)
  3. ----------
  4.     524288

  5. 已用时间:  00: 00: 00.20
  6. lastwinner@lw> select count(*) from (select rownum rn from dual connect by rownum<=power(2,20));
  7. select count(*) from (select rownum rn from dual connect by rownum<=power(2,20))
  8.                                             *
  9. 第 1 行出现错误:
  10. 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.   COUNT(*)
  3. ----------
  4.    1048576

  5. 已用时间:  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.   2  select count(*) from (select rownum rn from a, a);

  3.   COUNT(*)
  4. ----------
  5.    1048576

  6. 已用时间:  00: 00: 00.09

  7. 我们试着将1M加大到1G,在connect by方式下
  8. lastwinner@lw> with a as (select rownum rn from dual connect by rownum<=power(2,10))
  9.   2  select count(*) from (select rownum rn from a, a, a);

  10.   COUNT(*)
  11. ----------
  12. 1073741824

  13. 已用时间:  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.   COUNT(*)
  3. ----------
  4.   67108864

  5. 已用时间:  00: 00: 37.00
复制代码


如果直接构造到1G,那么时间差不多是16*37s这个级别。但如果通过笛卡尔积+xmltable的方式来构造


  1. lastwinner@lw> with a as (select rownum rn from xmltable('1 to 1024'))
  2.   2  select count(*) from (select rownum rn from a, a, a);

  3.   COUNT(*)
  4. ----------
  5. 1073741824

  6. 已用时间:  00: 01: 07.95
复制代码


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

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2013-6-14 15:18 | 显示全部楼层
现在再看看基本用纯表连接的方式来构造同样大小的序列,先来1M的
  1. lastwinner@lw> with b as (select 1 r from dual union all select 2 from dual),
  2.   2  c as (select rownum r from b,b,b,b,b,
  3.   3  b,b,b,b,b,
  4.   4  b,b,b,b,b,
  5.   5  b,b,b,b,b)
  6.   6  select count(*) from c;

  7.   COUNT(*)
  8. ----------
  9.    1048576

  10. 已用时间:  00: 00: 00.33
复制代码


再来64M的
  1. lastwinner@lw> ed
  2. 已写入 file afiedt.buf

  3.   1  with b as (select 1 r from dual union all select 2 from dual),
  4.   2  c as (select rownum r from b,b,b,b,b,
  5.   3  b,b,b,b,b,
  6.   4  b,b,b,b,b,
  7.   5  b,b,b,b,b,
  8.   6  b,b,b,b,b,b)
  9.   7* select count(*) from c
  10. lastwinner@lw> /

  11.   COUNT(*)
  12. ----------
  13.   67108864

  14. 已用时间:  00: 00: 16.62
复制代码

这个速度并不快,但已经比直接xmltable快了。
其实64M,即64*2^20可以表示为(2^5)^5*2,那我们来改写一下64M的sql
with b as (select 1 r from dual union all select 2 from dual),
c as (select rownum r from b,b,b,b,b),
d as (select rownum r from c,c,c,c,c,b)
select count(*) from d;


  1. lastwinner@lw> with b as (select 1 r from dual union all select 2 from dual),
  2.   2  c as (select rownum r from b,b,b,b,b),
  3.   3  d as (select rownum r from c,c,c,c,c,b)
  4.   4  select count(*) from d;

  5.   COUNT(*)
  6. ----------
  7.   67108864

  8. 已用时间:  00: 00: 04.53
复制代码

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


使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2013-6-14 15:56 | 显示全部楼层
但在构造到1G时,还是要慢一些
  1. lastwinner@lw> ed
  2. 已写入 file afiedt.buf

  3.   1  with b as (select 1 r from dual union all select 2 from dual),
  4.   2  c as (select rownum r from b,b,b,b,b),
  5.   3  d as (select rownum r from c,c,c,c,c,c)
  6.   4* select count(*) from d
  7. lastwinner@lw> /

  8.   COUNT(*)
  9. ----------
  10. 1073741824

  11. 已用时间:  00: 01: 11.48
复制代码

尝试相对较快的写法,多一层中间表
  1. lastwinner@lw> ed
  2. 已写入 file afiedt.buf

  3.   1  with b as (select 1 r from dual union all select 2 from dual),
  4.   2  c as (select rownum r from b,b,b),
  5.   3  d as (select rownum r from c,c,c),
  6.   4  e as (select rownum r from d,d,d,c)
  7.   5* select count(*) from e
  8. lastwinner@lw> /

  9.   COUNT(*)
  10. ----------
  11. 1073741824

  12. 已用时间:  00: 01: 06.89
复制代码

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

  1. lastwinner@lw> ed
  2. 已写入 file afiedt.buf

  3.   1  with b as (select 1 r from dual union all select 2 from dual),
  4.   2  c as (select rownum r from b,b,b,b,b),
  5.   3  d as (select rownum r from c,c),
  6.   4  e as (select rownum r from d,d,d)
  7.   5* select count(*) from e
  8. lastwinner@lw> /

  9.   COUNT(*)
  10. ----------
  11. 1073741824

  12. 已用时间:  00: 01: 05.21
复制代码

这时候我们将2^5=32换成直接构造出来的方式
  1. lastwinner@lw> ed
  2. 已写入 file afiedt.buf

  3.   1  with b as (select rownum r from dual connect by rownum<=power(2,5)),
  4.   2  c as (select rownum r from b,b),
  5.   3  d as (select rownum r from c,c,c)
  6.   4* select count(*) from d
  7. lastwinner@lw> /

  8.   COUNT(*)
  9. ----------
  10. 1073741824

  11. 已用时间:  00: 01: 05.07
复制代码

可见所耗费的时间差不多。
由此我们还可以得出,表连接的代价其实也是昂贵的,适当的减少表连接的次数,适当的使用with里的中间表,能有效提高系统性能。
再重复一下刚才构造64M(2^26)的场景
  1. lastwinner@lw> ed
  2. 已写入 file afiedt.buf

  3.   1  with b as (select 1 r from dual union all select 2 from dual),
  4.   2  c as (select rownum r from b,b,b,b,b,
  5.   3  b,b,b,b,b,
  6.   4  b,b,b,b,b,
  7.   5  b,b,b,b,b,
  8.   6  b,b,b,b,b,b)
  9.   7* select count(*) from c
  10. lastwinner@lw> /

  11.   COUNT(*)
  12. ----------
  13.   67108864

  14. 已用时间:  00: 00: 16.62
复制代码
总共25次的表连接,1层嵌套,让速度非常慢。提高一下(26=4*3*2+2*2),总共8次表连接,3层嵌套。
with b as (select 1 r from dual union all select 2 from dual),
c as (select rownum r from b,b,b,b),
d as (select rownum r from c,c,c),
e as (select rownum r from d,d,b,b)
select count(*) from e;

  1. lastwinner@lw> ed
  2. 已写入 file afiedt.buf

  3.   1  with b as (select 1 r from dual union all select 2 from dual),
  4.   2  c as (select rownum r from b,b,b,b),
  5.   3  d as (select rownum r from c,c,c),
  6.   4  e as (select rownum r from d,d,b,b)
  7.   5* select count(*) from e
  8. lastwinner@lw> /

  9.   COUNT(*)
  10. ----------
  11.   67108864

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

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

使用道具 举报

回复
论坛徽章:
40
授权会员
日期:2009-03-04 17:06:25最佳人气徽章
日期:2013-03-19 17:24:25SQL极客
日期:2013-12-09 14:13:35优秀写手
日期:2013-12-18 09:29:09ITPUB元老
日期:2015-03-04 13:33:34白羊座
日期:2016-03-11 13:49:34乌索普
日期:2017-11-17 11:40:00
发表于 2013-6-14 16:19 | 显示全部楼层
学习了,基本只用connect by

使用道具 举报

回复
论坛徽章:
118
2015年新春福章
日期:2015-03-06 11:58:18生肖徽章:猪
日期:2013-12-06 14:15:45生肖徽章:狗
日期:2013-12-06 14:15:45生肖徽章:鸡
日期:2013-12-06 14:15:45生肖徽章:猴
日期:2013-12-06 14:15:45生肖徽章:羊
日期:2013-12-06 14:15:45生肖徽章:马
日期:2013-12-06 14:15:45生肖徽章:蛇
日期:2013-12-06 14:15:45生肖徽章:龙
日期:2013-12-06 14:15:45生肖徽章:兔
日期:2013-12-06 14:15:45
发表于 2013-6-14 17:28 | 显示全部楼层
只用connect by的方式
也不会用到数量很大的时候,稍微多一点就表连接了。
耗时没在意过

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2013-6-14 18:39 | 显示全部楼层
打印出执行计划的实际成本

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2013-6-14 21:43 | 显示全部楼层
贴个PLSQL函数,以前从jonathan lewis博客看来的,只比你的方法慢一点点但是更灵活:

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

使用道具 举报

回复
论坛徽章:
8
玉兔
日期:2015-11-16 10:18:00铁扇公主
日期:2015-10-27 21:47:42九尾狐狸
日期:2015-12-11 22:31:15
发表于 2013-6-14 23:09 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2013-6-14 23:43 | 显示全部楼层
lugionline 发表于 2013-6-14 23:09
tally table?
http://www.sqlservercentral.com/articles/T-SQL/74118/

这网页居然要注册才能看。我只好打开它的源码看。

学习里面的方法:

WITH
  E1(N) AS (
            SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL UNION ALL  
            SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL UNION ALL
            SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
  E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows
  E8(N) AS (SELECT 1 FROM E4 a, E4 b)  -- 1*10^8 or 100,000,000 rows
SELECT COUNT(*) FROM E8
;

  COUNT(*)
----------
100000000

Elapsed: 00:00:07.05

select count(*) from table(generator(100000000));

  COUNT(*)
----------
100000000

Elapsed: 00:00:10.88

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表