對于數(shù)據(jù)庫中表的數(shù)據(jù)的 Web 顯示，如果沒有展示順序的需要，而且因?yàn)闈M足條件的記錄如此之多，就不得不對數(shù)據(jù)進(jìn)行分頁處理。常常用戶并不是對所有數(shù)據(jù)都感興趣的，或者大部分情況下，他們只看前幾頁。
通常有以下兩種分頁技術(shù)可供選擇。
|
1
2
3
4
5
6
7
|
Select * from (Select rownum rn,t.* from table t)Where rn>&minnum and rn<=&maxnum或者Select * from (Select rownum rn,t.* from table t rownum<=&maxnum)Where rn>&minnum |
看似相似的分頁語句，在響應(yīng)速度上其實(shí)有很大的差別。來看一個(gè)測試過程，首先創(chuàng)建一個(gè)測試表。
|
1
|
SQL>create table test as select * from dba_objects; |
并反復(fù)地插入相同數(shù)據(jù)。
|
1
|
SQL>insert into test select * from test; |
最后，查詢該表，可以看到該表的記錄數(shù)約為 80 萬條。
|
1
2
3
4
|
SQL> select count(*) from test COUNT(*)---------- 831104 |
現(xiàn)在分別采用兩種分頁方式，在第一種分頁方式中：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> select * from ( 2 select rownum rn,t.* from test t) 3 where rn>0 and rn <=50; 已選擇50行。已用時(shí)間: 00: 00: 01.03 Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=65 Bytes=12350) 1 0 VIEW (Cost=10 Card=65 Bytes=12350) 2 1 COUNT 3 2 TABLE ACCESS (FULL) OF 'TEST' (Cost=10 Card=65 Bytes=5590) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 10246 consistent gets 0 physical reads 0 redo size …… |
可以看到，這種方式查詢第一頁的一致性讀有 10246 個(gè)，結(jié)果滿足了，但是效率是很差的，如果采用第二種方式：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL> select * from ( 2 select rownum rn,t.* from test t 3 where rownum <=50) 4 where rn>0; 已選擇50行。已用時(shí)間: 00: 00: 01.00 Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=50 Bytes=9500) 1 0 VIEW (Cost=10 Card=50 Bytes=9500) 2 1 COUNT (STOPKEY) 3 2 TABLE ACCESS (FULL) OF 'TEST' (Cost=10 Card=65 Bytes=5590) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 82 consistent gets 0 physical reads 0 redo size …… |
得到了同樣的結(jié)果，一致性讀只有 82 個(gè)，從以上的例子可以看到，通過把 rownum 引入到第二層，卻得到了一個(gè)完全不一樣的執(zhí)行計(jì)劃，注意在執(zhí)行計(jì)劃中的 stopkey，它是 8i 引入的新操 作，這種操作專門為提取 Top n 的需求做了優(yōu)化。
從上面的例子可以再想到，因?yàn)?stopkey 的功能影響到了分頁的一致性讀的多少，會不會越往后翻頁速度就越慢呢？事實(shí)也的確如此，例如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select * from ( 2 select rownum rn,t.* from test t 3 where rownum <=10000) 4 where rn>9950; 已選擇50行。已用時(shí)間: 00: 00: 01.01 Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 2616 consistent gets 0 physical reads 0 redo size …… |
選擇靠后一點(diǎn)的數(shù)據(jù)時(shí)，邏輯讀開始變大，當(dāng)選擇到最后幾頁時(shí)，一致性讀已經(jīng)與上面的相似了。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select * from ( 2 select rownum rn,t.* from test t 3 where rownum <=800000) 4 where rn>799950; 已選擇50行。已用時(shí)間: 00: 00: 01.03 Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 10242 consistent gets 0 physical reads 0 redo size …… |
不過，所幸的是，大部分的用戶只看開始 5%的數(shù)據(jù)，而沒有興趣看最后面的數(shù)據(jù)，通過第二種改良的分頁技術(shù)，可以方便快速地顯示前面的數(shù)據(jù)，而且不會讓用戶感覺到慢。
總結(jié)
以上就是本文關(guān)于Oracle分頁查詢性能優(yōu)化代碼詳解的全部內(nèi)容，希望對大家有所幫助。歡迎大家參閱本站其他有關(guān)專題，有什么問題可以隨時(shí)留言，小編會及時(shí)回復(fù)大家的。
原文鏈接：http://blog.csdn.net/sunansheng/article/details/52586697
