《Oracle構(gòu)造序列的方法分析對(duì)比》要點(diǎn)：
本文介紹了Oracle構(gòu)造序列的方法分析對(duì)比，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。
編輯手記：關(guān)于Oracle的序列,相信大家并不陌生,但很多人平時(shí)只用到connect by 的方式來構(gòu)造序列,今天一起來學(xué)習(xí)更多的構(gòu)造序列的方法及每個(gè)方法的優(yōu)缺點(diǎn).
作者介紹
懷曉明,云和恩墨性能優(yōu)化專家.ITPUB社區(qū)版主,興趣廣泛,視野廣闊,目前專注于SQL審核與優(yōu)化工作,是一個(gè)細(xì)心敏銳的troubleshooter.擅長(zhǎng)數(shù)據(jù)庫和web的設(shè)計(jì)和開發(fā),精于故障診斷和處理.
正文
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’);
接下來我們從序列大小,構(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í)除了上述三種辦法,我們還可以使用笛卡爾積來構(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è)囍?strong>將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
已用時(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í)別.
但如果通過笛卡爾積＋xmltable的方式來構(gòu)造.
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
已用時(shí)間: ?00: 01: 07.95
這時(shí)間和connect by的差不多.以上測(cè)試,總的可見,在構(gòu)造較大序列時(shí),笛卡爾積的方式是最佳的,單純使用connect by會(huì)遭遇內(nèi)存不足,而單獨(dú)使用xmltable則會(huì)耗費(fèi)較多的時(shí)間.
現(xiàn)在再看看基本用純表連接的方式來構(gòu)造同樣大小的序列,先來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
再來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,那我們來改寫一下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)造出來的方式
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í)候,比如不超過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(*)
———-
67108864Elapsed: 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(*)
———-
67108864Elapsed: 00:00:06.32
文章來自微信公眾號(hào)：數(shù)據(jù)和云
轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/2359.html
