Oracle構(gòu)造序列的方法分析對(duì)比
關(guān)于Oracle的序列，相信大家并不陌生，但很多人平時(shí)只用到connect by 的方式來(lái)構(gòu)造序列，今天一起來(lái)學(xué)習(xí)更多的構(gòu)造序列的方法及每個(gè)方法的優(yōu)缺點(diǎn)。
Oracle構(gòu)造序列的方法隨著版本一直在變化。在9i之前的版本，常用的方法是：
- select rownum rn from all_objects where rownum<=xx;
從all_objects等系統(tǒng)視圖中去獲取序列的方式，雖然簡(jiǎn)單，但有一個(gè)致命的弱點(diǎn)是該視圖的sql非常復(fù)雜，嵌套層數(shù)很多，一旦應(yīng)用到真實(shí)案例中，極有可能碰到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’);
接下來(lái)我們從序列大小，構(gòu)造時(shí)間等方面對(duì)比分析這兩種方式。
1、先看connect by的方法
- lastwinner@lw> select count(*) from (select rownum rn from dual connect by rownum<=power(2,19));
- COUNT(*)
- ———-
- 524288
- 已用時(shí)間: 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 行出現(xiàn)錯(cuò)誤:
- ORA-30009: CONNECT BY 操作內(nèi)存不足
可見直接用connect by去構(gòu)造較大的序列時(shí)，消耗的資源很多，速度也快不到哪兒去。實(shí)際上2^20并不是一個(gè)很大的數(shù)字，就是1M而已。
但xmltable方式就不會(huì)耗這么多資源
- lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 1048576’));
- COUNT(*)
- ———-
- 1048576
- 已用時(shí)間: 00: 00: 00.95
其實(shí)除了上述三種辦法，我們還可以使用笛卡爾積來(lái)構(gòu)造序列。如果換成笛卡爾連接的方式，那么構(gòu)造2^20時(shí)，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
- 已用時(shí)間: 00: 00: 00.09
我們?cè)囍鴮?M加大到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
- 已用時(shí)間: 00: 01: 07.37
耗時(shí)高達(dá)1分鐘還多，再看看xmltable方式，考慮到1M的時(shí)候耗時(shí)就達(dá)到0.95秒，因此這里只測(cè)試1/16*1G，即64M的情況
- lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 67108864’));
- COUNT(*)
- ———-
- 67108864
- 已用時(shí)間: 00: 00: 37.00
如果直接構(gòu)造到1G，那么時(shí)間差不多是16*37s這個(gè)級(jí)別。
但如果通過(guò)笛卡爾積+xmltable的方式來(lái)構(gòu)造。
- lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 67108864’));
- COUNT(*)
- ———-
- 67108864
- 已用時(shí)間: 00: 00: 37.00
這時(shí)間和connect by的差不多。以上測(cè)試，總的可見，在構(gòu)造較大序列時(shí)，笛卡爾積的方式是***的，單純使用connect by會(huì)遭遇內(nèi)存不足，而單獨(dú)使用xmltable則會(huì)耗費(fèi)較多的時(shí)間。
現(xiàn)在再看看基本用純表連接的方式來(lái)構(gòu)造同樣大小的序列，先來(lái)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
- 已用時(shí)間: 00: 00: 00.33
再來(lái)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
- 已用時(shí)間: 00: 00: 16.62
這個(gè)速度并不快，但已經(jīng)比直接xmltable快了。
其實(shí)64M，即64*2^20可以表示為(2^5)^5*2，那我們來(lái)改寫一下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
- 已用時(shí)間: 00: 00: 04.53
可以看到，從16s到4s，已經(jīng)快了很多。這個(gè)示例告訴我們，中間表c 在提高速度方面起到了很好的作用。
但在構(gòu)造到1G時(shí)，還是要慢一些
- 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
- 已用時(shí)間: 00: 01: 11.48
嘗試相對(duì)較快的寫法，多一層中間表
- 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
- 已用時(shí)間: 00: 01: 06.89
更快一點(diǎn)(思路，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
- 已用時(shí)間: 00: 01: 05.21
這時(shí)候我們將2^5=32換成直接構(gòu)造出來(lái)的方式
- 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
- 已用時(shí)間: 00: 01: 05.07
可見所耗費(fèi)的時(shí)間差不多。
由此我們還可以得出，表連接的代價(jià)其實(shí)也是昂貴的，適當(dāng)?shù)臏p少表連接的次數(shù)，適當(dāng)?shù)氖褂脀ith里的中間表，能有效提高系統(tǒng)性能。
再重復(fù)一下剛才構(gòu)造64M(2^26)的場(chǎng)景
- 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
- 已用時(shí)間: 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
- 已用時(shí)間: 00: 00: 04.00
效率提升4倍。要注意在這個(gè)案例中并非表連接越少越好，嵌套層數(shù)也是需要關(guān)注的指標(biāo)。執(zhí)行計(jì)劃有興趣的同學(xué)自己去看吧，我就不列了，上例中，系統(tǒng)生成的中間表有3個(gè)。
最終結(jié)論，構(gòu)造較大序列時(shí)，例如同樣是構(gòu)造出64M的序列，oracle在處理時(shí)，用表連接的方式明顯占優(yōu)。但考慮到書寫的便利性，因此在構(gòu)造較小序列的時(shí)候，比如不超過(guò)1K的序列，那么直接用connect by或xmltable的方式就好了。
附：newkid 回復(fù)方法，表示更靈活，有興趣的同學(xué)可以嘗試：
- 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
