Oracle中怎么构造序列

2025-03-24 技术教程

本篇文章给大家分享的是有关Oracle中怎么构造序列，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

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

selectrownumrnfromall_objectswhererownum<=xx;

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

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

selectrownumrnfromdualconnectbyrownum<=xx;

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

selectrownumrnfromxmltable(‘1toxx’);

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

1、先看connect by的方法

lastwinner@lw>selectcount(*)from(selectrownumrnfromdualconnectbyrownum<=power(2,19));COUNT(*)———-524288已用时间:00:00:00.20lastwinner@lw>selectcount(*)from(selectrownumrnfromdualconnectbyrownum<=power(2,20));selectcount(*)from(selectrownumrnfromdualconnectbyrownum<=power(2,20))*第1行出现错误:ORA-30009:CONNECTBY操作内存不足

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

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

lastwinner@lw>selectcount(*)from(selectrownumrnfromxmltable(‘1to1048576’));COUNT(*)———-1048576已用时间:00:00:00.95

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

lastwinner@lw>withaas(selectrownumrnfromdualconnectbyrownum<=power(2,10))2selectcount(*)from(selectrownumrnfroma,a);COUNT(*)———-1048576已用时间:00:00:00.09

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

lastwinner@lw>withaas(selectrownumrnfromdualconnectbyrownum<=power(2,10))2selectcount(*)from(selectrownumrnfroma,a,a);COUNT(*)———-1073741824已用时间:00:01:07.37

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

lastwinner@lw>selectcount(*)from(selectrownumrnfromxmltable(‘1to67108864’));COUNT(*)———-67108864已用时间:00:00:37.00

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

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

lastwinner@lw>selectcount(*)from(selectrownumrnfromxmltable(‘1to67108864’));COUNT(*)———-67108864已用时间:00:00:37.00

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

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

lastwinner@lw>withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b,b,b,3b,b,b,b,b,4b,b,b,b,b,5b,b,b,b,b)6selectcount(*)fromc;COUNT(*)———-1048576已用时间:00:00:00.33

再来64M的

lastwinner@lw>ed已写入fileafiedt.buf1withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b,b,b,3b,b,b,b,b,4b,b,b,b,b,5b,b,b,b,b,6b,b,b,b,b,b)7*selectcount(*)fromclastwinner@lw>/COUNT(*)———-67108864已用时间:00:00:16.62

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

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

lastwinner@lw>withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b,b,b),3das(selectrownumrfromc,c,c,c,c,b)4selectcount(*)fromd;COUNT(*)———-67108864已用时间:00:00:04.53

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

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

lastwinner@lw>ed已写入fileafiedt.buf1withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b,b,b),3das(selectrownumrfromc,c,c,c,c,c)4*selectcount(*)fromdlastwinner@lw>/COUNT(*)———-1073741824已用时间:00:01:11.48

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

lastwinner@lw>ed已写入fileafiedt.buf1withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b),3das(selectrownumrfromc,c,c),4eas(selectrownumrfromd,d,d,c)5*selectcount(*)fromelastwinner@lw>/COUNT(*)———-1073741824已用时间:00:01:06.89

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

lastwinner@lw>ed已写入fileafiedt.buf1withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b,b,b),3das(selectrownumrfromc,c),4eas(selectrownumrfromd,d,d)5*selectcount(*)fromelastwinner@lw>/COUNT(*)———-1073741824已用时间:00:01:05.21

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

lastwinner@lw>ed已写入fileafiedt.buf1withbas(selectrownumrfromdualconnectbyrownum<=power(2,5)),2cas(selectrownumrfromb,b),3das(selectrownumrfromc,c,c)4*selectcount(*)fromdlastwinner@lw>/COUNT(*)———-1073741824已用时间:00:01:05.07

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

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

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

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

lastwinner@lw>ed已写入fileafiedt.buf1withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b,b),3das(selectrownumrfromc,c,c),4eas(selectrownumrfromd,d,b,b)5*selectcount(*)fromelastwinner@lw>/COUNT(*)———-67108864已用时间:00:00:04.00

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

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

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

createorreplacefunctiongenerator(npls_integer)returnsys.odcinumberlistpipelinedismpls_integer:=trunc(n/10);rpls_integer:=n–10*m;beginforiin1..mlooppiperow(null);piperow(null);piperow(null);piperow(null);piperow(null);piperow(null);piperow(null);piperow(null);piperow(null);piperow(null);endloop;foriin1..rlooppiperow(null);endloop;end;/alterfunctiongeneratorcompileplsql_code_type=native;SQL>selectcount(*)fromtable(generator(67108864));COUNT(*)———-67108864Elapsed:00:00:06.68SQL>withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b,b),3das(selectrownumrfromc,c,c),4eas(selectrownumrfromd,d,b,b)5selectcount(*)frome;COUNT(*)———-67108864Elapsed:00:00:06.32

以上就是Oracle中怎么构造序列，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。