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

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

作者介绍

怀晓明,云和恩墨性能优化专家。ITPUB社区版主,兴趣广泛,视野广阔,目前专注于SQL审核与优化工作,是一个细心敏锐的troubleshooter。擅长数据库和web的设计和开发,精于故障诊断和处理。

正文

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> with a as (select rownum rn from xmltable('1 to 1024')) 2 select count(*) from (select rownum rn from a, a, a); COUNT(*) ---------- 1073741824 已用时间: 00: 01: 07.95

这时间和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

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-07-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java技术

动态代理之投鞭断流!看一下MyBatis的底层实现原理!

一日小区漫步,我问朋友:Mybatis中声明一个interface接口,没有编写任何实现类,Mybatis就能返回接口实例,并调用接口方法返回数据库数据,你知道...

512
来自专栏数据小魔方

高级筛选到底有多“高级”!

今天跟大家分享excel筛选功能中隐藏的高级筛选功能! excel中的筛选窗口中,一直隐藏着一个不起眼的小菜单——高级:(如下图) ? 按照微软软件一贯风格,藏...

3205
来自专栏灯塔大数据

每周学点大数据 | No.38平均数计算

No.38期 ‍平均数计算‍ Mr. 王:再来看一个例子——均数计算。我希望借助这个例子,仔细讲解一下关于combiner 的问题。 小可:从前面的例子可以看出...

3448
来自专栏木子昭的博客

用chrome学编程利用chrome学编程,一切都变得简单...

利用chrome学编程,一切都变得简单... 用chrome学编程,安装浏览器就可以上手,javascript本身也足够强大,前后端通吃,容易出成果,有了成果...

4297
来自专栏我的博客

领域专用语言(DSL)和通用编程语言(GPL)

DSL解释 领域专用语言(Domain Specific Language/DSL) DSL 通过在表达能力上做的妥协换取在某一领域内的高效(世界级软件开发...

37113
来自专栏Java呓语

权限之数据权限概念原型实现抽象

随着业务的演进,逐渐衍生出精细化管理数据的诉求。我遇到的业务场景是在企业级数据管理中,对不同职级的员工展示不同的数据。我的业务上的诉求是对SELECT进行权限控...

1002
来自专栏Python中文社区

多种方法爬取猫眼电影并分析(附代码)

摘要: 作为小白,爬虫可以说是入门python最快和最容易获得成就感的途径。因为初级爬虫的套路相对固定,常见的方法只有几种,比较好上手。选取网页结构较为简单的猫...

2203
来自专栏牛客网

美团JAVA开发4面面经

【每日一语】不要回头。那个时候,是自己下定了决心,自己选择了道路了吧。那就不要道歉,不要哭,不要彷徨,只注视着前方前进就好。——《银魂》

962
来自专栏SAP最佳业务实践

想学FM系列(19)-SAP FM模块:派生规则推导策略(2)-派生规则推导步骤-分配、表格查询

4.1.2 分配 分配:是推导过程中给某一字段赋值,如同 A = B 一样赋值。字段可以是源数据,也可以是辅助数据,也可以是目标数据。设置见下图 定义: ? ①...

4956
来自专栏灯塔大数据

每周学点大数据 | No.36并行算法

No.36期 ‍并行算法‍ Mr. 王:‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍今天我们来谈一个新的话题——并行算法。 小可:并行?并行是不是说,一个任务由多个人...

27410

扫码关注云+社区