关于Oracle序列，相信大家都不陌生，但是很多人通常只会使用connectby的方式来构造序列。今天，我们就来了解更多构造序列的方法，以及每种方法的优缺点。Oracle构建序列的方式因版本而异。在9i之前的版本中，常用的方法是：selectrownumrnfromall_objectswhererownum<=xx;all_objects等系统视图获取序列的方式很简单，但是有个致命弱点就是这个视图的sql非常复杂，嵌套层数很多，一旦应用到实际案例中，很可能会遇到Oracle自身的bug，所以这个方法忽略，直接通过。2、9i以后我们使用connectbyselectrownumrnfromdualconnectbyrownum<=xx;3、由于10g开始支持XML，还可以使用如下方法：selectrownumrnfromxmltable('1toxx');接下来我们从序列大小、构建时间等方面对两者进行对比分析。1.先看connectbylastwinner@lw>selectcount(*)from(selectrownumrnfromdualconnectbyrownum<=power(2,19));COUNT(*)————-524288的方法Elapsedtime:00:00:00.20lastwinner@lw>selectcount(*)from(selectrownumrnfromdualconnectbyrownum<=power(2,20));selectcount(*)from(selectrownumrnfromdualconnectbyrownum<=power(2,20))*第1行错误：ORA-30009：CONNECTBY操作内存不足可见直接使用connectby构造更大的序列时，资源消耗很大，速度也快不了多少。其实2^20并不是一个很大的数，正好1M。但是xmltable方法不会消耗那么多资源lastwinner@lw>selectcount(*)from(selectrownumrnfromxmltable('1to1048576'));COUNT(*)————-1048576elapsedtime:00:00:00.95其实除此之外对上面的三个这样，我们也可以用笛卡尔积来构造序列。如果换成笛卡尔连接方式，那么构造2^20时，connectby也是oklastwinner@lw>withaas(selectrownumrnfromdualconnectbyrownum<=power(2,10))2selectcount(*)from(selectrownumrnfroma,a);COUNT(*)————-1048576Elapsedtime:00:00:00.09我们尝试增加1M到1G，在connectbymodelastwinner@lw>withaas(selectrownumrnfromdualconnectbyrownum<=power(2,10))2selectcount(*)from(selectrownumrnfroma,a,a);COUNT(*)——————1073741824Elapsedtime:00:01:07.37耗时1分多钟，再看xmltable方法。考虑到1M需要时间达到0.95秒，所以这里只测试1/16*1G，即64Mlastwinner@lw>selectcount(*)from(selectrownumrnfromxmltable('1to67108864'));COUNT(*)————-67108864elapsedtime:00:00:37.00如果直接构造到1G，那么时间差不多在16*37s的水平。但是如果是笛卡尔积+xmltable构造的。lastwinner@lw>selectcount(*)from(selectrownumrnfromxmltable(‘1to67108864’));COUNT(*)————-67108864Elapsedtime:00:00:37.00这个时间和connectby类似。从以上测试可以看出，在构建大序列时，笛卡尔积法是最好的。单纯使用connectby会遇到内存不足的情况，单独使用xmltable会消耗更多的时间。现在让我们看看使用纯表连接构造相同大小的序列的基本方法。首先，1Mlastwinner@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然后64Mlastwinner@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(*)————-67108864Elapsedtime:00:00:16.62这个速度不算快，但是已经比直接xmltable快了。其实64M，也就是64*2^20可以表示为(2^5)^5*2，那么我们把64M改写一下sqllastwinner@lw>withbas(select1rfromdualunionallselect2fromdual),2cas(selectrownumrfromb,b,b,b,b),3das(selectrownumrfromc,c,c,c,c,b)4selectcount(*)fromd;COUNT(*)————-67108864Elapsedtime: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(*)————-1073741824Elapsedtime: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(*)————-1073741824Elapsedtime: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(*)————-1073741824elapsedtime:00:01:05.07可以看出，花费的时间差不多。由此我们也可以得出结论，tablejoin的开销其实是很大的。适当减少tablejoin的个数，适当使用with中的中间表，可以有效提升系统性能。重复一遍64M（2^26）的场景lastwinner@lw>ed已经写入文件afiedt.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(*)————-67108864Elapsedtime:00:00:16.62一共25个tablejoin，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的序列，还是直接使用connectby或者xmltable比较好。附：newkid回复方法，比较灵活，感兴趣的同学可以试试：createorreplacefunctiongenerator(npls_integer)returnssys.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(select1rfromdualunionalallselect),2fromdu2cas(selectrownumrfromb,b,b,b),3das(selectrownumrfromc,c,c),4eas(selectrownumrfromd,d,b,b)5selectcount(*)from;COUNT(*)————67108864Elapsed:00:00:06.32
