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')); 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方式下

  2  select count(*) from (select rownum rn from a, a, a); 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'));   67108864 已用時間:  00: 00: 37.00

如果直接構造到1g，那麼時間差不多是16*37s這個級别。

但如果通過笛卡爾積＋xmltable的方式來構造。

lastwinner@lw> with a as (select rownum rn from xmltable('1 to 1024')) 已用時間:  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; 已用時間:  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),   5  b,b,b,b,b,   6  b,b,b,b,b,b)   7* select count(*) from c lastwinner@lw> / 已用時間:  00: 00: 16.62

這個速度并不快，但已經比直接xmltable快了。

其實64m，即64*2^20可以表示為(2^5)^5*2，那我們來改寫一下64m的sql

  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; 已用時間:  00: 00: 04.53

可以看到，從16s到4s，已經快了很多。這個示例告訴我們，中間表c 在提高速度方面起到了很好的作用。

但在構造到1g時，還是要慢一些

  3  d as (select rownum r from c,c,c,c,c,c)   4* select count(*) from d 已用時間:  00: 01: 11.48

嘗試相對較快的寫法，多一層中間表

  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 已用時間:  00: 01: 06.89

更快一點（思路，32^2=1024, 1g=2^30=(2^5)^6=((2^5)^2)^3 。）

  3  d as (select rownum r from c,c),   4  e as (select rownum r from d,d,d) 已用時間:  00: 01: 05.21

這時候我們将2^5＝32換成直接構造出來的方式

  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) 已用時間:  00: 01: 05.07

可見所耗費的時間差不多。

由此我們還可以得出，表連接配接的代價其實也是昂貴的，适當的減少表連接配接的次數，适當的使用with裡的中間表，能有效提高系統性能。

再重複一下剛才構造64m（2^26）的場景

總共25次的表連接配接，1層嵌套，讓速度非常慢。提高一下（26=4*3*2+2*2），總共8次表連接配接，3層嵌套。

  2  c as (select rownum r from b,b,b,b),   4  e as (select rownum r from d,d,b,b) 已用時間:  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);   end loop;   for i in 1 .. r 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; elapsed: 00:00:06.32