244
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
本文出自數據和雲公眾號，原文鏈接
最後更新：2017-07-17 17:03:29