本篇文章給大家分享的是有關(guān)Oracle中怎么構(gòu)造序列，小編覺得挺實用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

從策劃到設(shè)計制作，每一步都追求做到細膩，制作可持續(xù)發(fā)展的企業(yè)網(wǎng)站。為客戶提供成都網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)、網(wǎng)站策劃、網(wǎng)頁設(shè)計、域名注冊、虛擬空間、網(wǎng)絡(luò)營銷、VI設(shè)計、 網(wǎng)站改版、漏洞修補等服務(wù)。為客戶提供更好的一站式互聯(lián)網(wǎng)解決方案,以客戶的口碑塑造優(yōu)易品牌,攜手廣大客戶,共同發(fā)展進步。

Oracle構(gòu)造序列的方法隨著版本一直在變化。在9i之前的版本，常用的方法是：

select rownum rn from all_objects where rownum<=xx;

從all_objects等系統(tǒng)視圖中去獲取序列的方式，雖然簡單，但有一個致命的弱點是該視圖的sql非常復(fù)雜，嵌套層數(shù)很多，一旦應(yīng)用到真實案例中，極有可能碰到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)造時間等方面對比分析這兩種方式。

1、先看connect by的方法

lastwinner@lw> select count(*) from (select rownum rn from dual connect by rownum<=power(2,19));  COUNT(*)  &mdash;&mdash;&mdash;-  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 行出現(xiàn)錯誤:  ORA-30009: CONNECT BY 操作內(nèi)存不足

可見直接用connect by去構(gòu)造較大的序列時，消耗的資源很多，速度也快不到哪兒去。實際上2^20并不是一個很大的數(shù)字，就是1M而已。

但xmltable方式就不會耗這么多資源

lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 1048576’));  COUNT(*)  ———-  1048576  已用時間: 00: 00: 00.95

其實除了上述三種辦法，我們還可以使用笛卡爾積來構(gòu)造序列。如果換成笛卡爾連接的方式，那么構(gòu)造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(*)  &mdash;&mdash;&mdash;-  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(*)  &mdash;&mdash;&mdash;-  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

如果直接構(gòu)造到1G，那么時間差不多是16*37s這個級別。

但如果通過笛卡爾積+xmltable的方式來構(gòu)造。

lastwinner@lw> select count(*) from (select rownum rn from xmltable(‘1 to 67108864’));  COUNT(*)  ———-  67108864  已用時間:  00: 00: 37.00

這時間和connect by的差不多。以上測試，總的可見，在構(gòu)造較大序列時，笛卡爾積的方式是***的，單純使用connect  by會遭遇內(nèi)存不足，而單獨使用xmltable則會耗費較多的時間。

現(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  已用時間:  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

這個速度并不快，但已經(jīng)比直接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，已經(jīng)快了很多。這個示例告訴我們，中間表c 在提高速度方面起到了很好的作用。

但在構(gòu)造到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換成直接構(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(*)  &mdash;&mdash;&mdash;-  1073741824  已用時間:  00: 01: 05.07

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

由此我們還可以得出，表連接的代價其實也是昂貴的，適當(dāng)?shù)臏p少表連接的次數(shù)，適當(dāng)?shù)氖褂脀ith里的中間表，能有效提高系統(tǒng)性能。

再重復(fù)一下剛才構(gòu)造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倍。要注意在這個案例中并非表連接越少越好，嵌套層數(shù)也是需要關(guān)注的指標(biāo)。執(zhí)行計劃有興趣的同學(xué)自己去看吧，我就不列了，上例中，系統(tǒng)生成的中間表有3個。

最終結(jié)論，構(gòu)造較大序列時，例如同樣是構(gòu)造出64M的序列，oracle在處理時，用表連接的方式明顯占優(yōu)。但考慮到書寫的便利性，因此在構(gòu)造較小序列的時候，比如不超過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

以上就是Oracle中怎么構(gòu)造序列，小編相信有部分知識點可能是我們?nèi)粘９ぷ鲿姷交蛴玫降?。希望你能通過這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

分享題目：Oracle中怎么構(gòu)造序列
標(biāo)題鏈接：http://m.rwnh.cn/article40/ggdceo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站建設(shè)、小程序開發(fā)、虛擬主機、網(wǎng)站改版、用戶體驗、App設(shè)計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)