共計(jì) 6223 個(gè)字符，預(yù)計(jì)需要花費(fèi) 16 分鐘才能閱讀完成。
本篇文章給大家分享的是有關(guān) Oracle 中怎么構(gòu)造序列，丸趣 TV 小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著丸趣 TV 小編一起來(lái)看看吧。
Oracle 構(gòu)造序列的方法隨著版本一直在變化。在 9i 之前的版本，常用的方法是：
select rownum rn from all_objects where rownum從 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 rownum3、自從 10g 開(kāi)始支持 XML 后，還可以使用以下方式：
select rownum rn from xmltable(lsquo;1 to xx rsquo;);接下來(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(*) mdash; mdash; mdash;- 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)存不足
可見(jiàn)直接用 connect by 去構(gòu)造較大的序列時(shí)，消耗的資源很多，速度也快不到哪兒去。實(shí)際上 2^20 并不是一個(gè)很大的數(shù)字，就是 1M 而已。
但 xmltable 方式就不會(huì)耗這么多資源
lastwinner@lw select count(*) from (select rownum rn from xmltable( lsquo;1 to 1048576 rsquo;)); COUNT(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 1048576 已用時(shí)間: 00: 00: 00.09
我們?cè)囍鴮?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(*) mdash; mdash; mdash;- 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( lsquo;1 to 67108864 rsquo;)); COUNT(*) mdash; mdash; mdash;- 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( lsquo;1 to 67108864 rsquo;)); COUNT(*) mdash; mdash; mdash;- 67108864 已用時(shí)間: 00: 00: 37.00這時(shí)間和 connect by 的差不多。以上測(cè)試，總的可見(jiàn)，在構(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(*) mdash; mdash; mdash;- 1048576 已用時(shí)間: 00: 00: 00.33再來(lái) 64M 的
lastwinner@lw ed 已寫(xiě)入 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(*) mdash; mdash; mdash;- 67108864 已用時(shí)間: 00: 00: 16.62這個(gè)速度并不快，但已經(jīng)比直接 xmltable 快了。
其實(shí) 64M，即 64*2^20 可以表示為 (2^5)^5*2，那我們來(lái)改寫(xiě)一下 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(*) mdash; mdash; mdash;- 67108864 已用時(shí)間: 00: 00: 04.53可以看到，從 16s 到 4s，已經(jīng)快了很多。這個(gè)示例告訴我們，中間表 c 在提高速度方面起到了很好的作用。
但在構(gòu)造到 1G 時(shí)，還是要慢一些
lastwinner@lw ed 已寫(xiě)入 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(*) mdash; mdash; mdash;- 1073741824 已用時(shí)間: 00: 01: 11.48嘗試相對(duì)較快的寫(xiě)法，多一層中間表
lastwinner@lw ed 已寫(xiě)入 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(*) mdash; mdash; mdash;- 1073741824 已用時(shí)間: 00: 01: 06.89更快一點(diǎn) (思路，32^2=1024, 1G=2^30=(2^5)^6=((2^5)^2)^3。)
lastwinner@lw ed 已寫(xiě)入 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(*) mdash; mdash; mdash;- 1073741824 已用時(shí)間: 00: 01: 05.21這時(shí)候我們將 2^5=32 換成直接構(gòu)造出來(lái)的方式
lastwinner@lw ed 已寫(xiě)入 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(*) mdash; mdash; mdash;- 1073741824 已用時(shí)間: 00: 01: 05.07可見(jiàn)所耗費(fèi)的時(shí)間差不多。
由此我們還可以得出，表連接的代價(jià)其實(shí)也是昂貴的，適當(dāng)?shù)臏p少表連接的次數(shù)，適當(dāng)?shù)氖褂?with 里的中間表，能有效提高系統(tǒng)性能。
再重復(fù)一下剛才構(gòu)造 64M(2^26) 的場(chǎng)景
lastwinner@lw ed 已寫(xiě)入 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(*) mdash; mdash; mdash;- 67108864 已用時(shí)間: 00: 00: 16.62總共 25 次的表連接，1 層嵌套，讓速度非常慢。提高一下 (26=4*3*2+2*2)，總共 8 次表連接，3 層嵌套。
lastwinner@lw ed 已寫(xiě)入 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(*) mdash; mdash; mdash;- 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)。但考慮到書(shū)寫(xiě)的便利性，因此在構(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 ndash; 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(*) mdash; mdash; mdash;- 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(*) mdash; mdash; mdash;- 67108864 Elapsed: 00:00:06.32以上就是 Oracle 中怎么構(gòu)造序列，丸趣 TV 小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注丸趣 TV 行業(yè)資訊頻道。
