oracle 高效分頁
oracle高效分頁
什么是分頁查詢
對于基于Web的應用而言，對查詢的結果集進行分頁是一個比較常見的需求。假設瀏覽器界面每頁可以顯示10條記錄，最初界面顯示頭10條記錄給用 戶，當終端用戶點擊“下一頁”按鈕時，界面顯示接下來的10條記錄。一般來說，Web后臺服務程序并不是一次性的把所有符合條件的記錄都返回給瀏覽器，再 由瀏覽器應用程序對查詢結果進行分頁。現在的普遍做法都是：當用戶要瀏覽下一頁時，瀏覽器重新從WEB后臺服務器取出下10條記錄。
對于采用了數據庫的WEB應用來說，如何對查詢的結果進行分頁就有兩種實現方式，一種是WEB后臺程序把全部查詢結果取到內存中，由它實現分頁。另一種是每次只從數據庫取出10條記錄，由數據庫實現分頁。
這兩種分頁方式各有優缺點，有時可能把這兩種方式結合起來應用。在這里，我主要介紹一下如何在Oracle數據庫中實現分頁查詢。
如何實現分頁查詢
認識ROWNUM
Oracle的ROWNUM偽列是實現結果集分頁的關鍵，可能有很多人對于ROWNUM偽列到底代表什么還不太清楚，有人甚至認為它是數據庫表中記 錄的編號。下面我引用在ASKTOM網站上的他一個例子幫助大家認識一下ROWNUM到底為何物。為了幫助大家理解，我建了一個測試表，然后再插入20條 測試數據，當前原例子中查詢語句表名和字段也做了相應的修改。
--建測試表
create table t_testrownum
( rid number, rvalue varchar2(30))
--插入測試數據
begin
insert into t_testrownum values(1, 'aaaa');
insert into t_testrownum values(2, 'aaaa');
insert into t_testrownum values(3, 'aaaa1');
insert into t_testrownum values(4, 'aaaa');
insert into t_testrownum values(5, 'aaaa');
insert into t_testrownum values(6, 'aaaa');
insert into t_testrownum values(7, 'aaaa');
insert into t_testrownum values(8, 'aaaa4');
insert into t_testrownum values(9, 'aaaa');
insert into t_testrownum values(10, 'aaaa');
insert into t_testrownum values(11, 'aaaa');
insert into t_testrownum values(12, 'aaaa');
insert into t_testrownum values(13, 'aaaa5');
insert into t_testrownum values(14, 'aaaa');
insert into t_testrownum values(15, 'aaaa');
insert into t_testrownum values(16, 'aaaa');
insert into t_testrownum values(17, 'aaaa');
insert into t_testrownum values(18, 'aaaa8');
insert into t_testrownum values(19, 'aaaa');
insert into t_testrownum values(20, 'aaaa');
end;
例1．
select * from t_testrownum where rownum = 1; 返回結果集的第一條記錄。那么select * from T where rownum = 2;應該返回結果集的第二條記錄。可是實際上第二個查詢語句不會返回任何記錄，為什么呢？
類似的例子還有：
select * from T where rownum >= 1 and rownum <= 5; 返回前5條記錄，而
select * from T where rownum >= 2 and rownum <= 5; 無記錄返回。
其實：ROWNUM并是記錄編號，而是Oracle在向外輸出結果集中的記錄時給它賦的一個順序號。當不在查詢語句中限制ROWNUM時，其處理邏輯如下所示：
rownum = 1
for x in ( select * from T )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;
當限制ROWNUM時，我們對比一下下面兩個查詢的執行計劃：
語句1：select * from t_testrownum;
語句1的執行計劃：
SELECT STATEMENT Optimizer Mode=CHOOSE TABLE
ACCESS FULL USDPD502.T_TESTROWNUM
語句2：select * from t_testrownum where rownum <= 10;
語句2的執行計劃
SELECT STATEMENT Optimizer Mode=CHOOSE
COUNT STOPKEY
TABLE ACCESS FULL USDPD502.T_TESTROWNUM
通過對比，我們可以看出語句2的執行計劃中增加了一條‘COUNT STOPKEY’，該句的意思是當rownum已超出指定范圍時，停止輸出，其處理邏輯如下：
rownum = 1
for x in ( select * from T )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
if ( rownum已超出指定范圍 )
then
跳出循環
end if;
end loop;
至此，我們就可以解釋上面兩個例子中的問題了。當我們限制rownum=1時，第一條記錄滿足該條件，輸出該記錄，rownum增1，由于 rownum已超出范圍，停止輸出。當我們限制rownum=2時，由于第一條記錄不滿足條件，不輸出該記錄，rownum也不增加。接著取第二條記錄， 由于rownum此時還是1，不滿足條件，同樣也不輸出，如此直到遍歷全部記錄結束循環。
基本的分頁查詢
當知道rownum是怎么回事后，我們就可以利用它來實現分頁查詢了。假如我們想從表T中取出第11條到第20條記錄，在未透徹了解ROWNUM之前，許多人可能會寫出下面的查詢語句。
--語句1
select * t_testrownum a where rownum >= 11 and rownum <= 20;
通過前面的分析，我們知道，這樣的寫法是錯誤的。所以，我們把它修改為如下的寫法。
--語句2
select *
from ( select a.*, rownum r from t_testrownum a )
where r>= 11 and r <= 20
該語句的輸出結果是正確的，它的內層查詢先從表t_testrownum中查詢出所有記錄，同時為每條記錄賦一個順序編號r，外層查詢再限制只選取編號為11到20之間的記錄。
從查詢效率上考慮一下，如果我們只需要得到第11到20條之間的記錄，那么在內層查詢中就可以利用rownum限制內層查詢輸出的記錄數。修改后的語句如下：
--語句3
select *
from ( select a.*, rownum r
from t_testrownum a where rownum <= 20)
where r>= 11
需要排序的分頁查詢
有人會想，排序那還不簡單嗎，加上order by 子句就行了。
--語句4
select *
from ( select a.*, rownum r from t_testrownum a where rownum <= 20 order by rvalue)
where r>= 11
我們都知道order by是對輸出的結果集進行排序，而不是先排序然后輸出結果集。語句4的實際效果是，從表t_testrownum中取出前20行記錄，然后按照rvalue字段排序，輸出排序編號大于等于11的記錄。
只對前20條記錄進行排序顯然不是我們所期望的，為避免這個問題，有人可能會把上面的語句做如下修改：
--語句5
select *
from ( select a.*, rownum r from t_testrownum a order by rvalue ）
where r>= 11 and r <= 20
同樣，由于rownum 在排序之前就確定了，我們得到得記錄并不是排序后的第11到20條記錄，而是排序前的第11到20條記錄。為得到我們期望的結果，我們必須把rownum r放到order by的外面。修改后的查詢語句如下。
--語句6
select *
from ( select b.*, rownum r
from ( select a.*
from t_testrownum a order by rvalue ) b
where rownum <= 20 )
where r>= 11
如果排序字段rvalue的值在表t_testrownum中是唯一的，那么上面的語句從功能實現上來說，就沒什么問題了。但是如果rvalue字 段的值不唯一，假設按rvalue排序后，前1到20條記錄的rvalue字段的值是相同的，我們先查出第1到10條記錄，然后再查出第11到20條記 錄，這是我們會發現，同一條記錄可能同時出現在這兩個查詢結果集中。這是為什么呢。一開始，我認為是Oracle采用的排序算法是不穩定的，兩個相同的值 在兩次排序中的順序是不固定的。但是我們把語句select a.* from t_testrownum a order by rvalue執行10次，卻發現輸出結果集的排序順序都是一致的。那么是什么導致排序不一致呢。為此，我們觀察了一下語句6的執行計劃：
SELECT STATEMENT, GOAL = CHOOSE
VIEW Object owner=USDPD502
COUNT STOPKEY
VIEW Object owner=USDPD502
SORT ORDER BY STOPKEY
TABLE ACCESS FULL Objectowner=USDPD502 Objectname=T_TESTROWNUM
從執行計劃中我們看出，執行計劃的第2步是“SORT ORDER BY STOPKEY”，它表示
其并不是對所有符合條件的記錄完全排序，而是僅僅找到符合排序條件的指定條數的記錄，比如我們限制rownum <= 20，則只需找到排序在前20位的記錄。記得在Oracle的官方文檔上我曾經見到Oracle聲稱其排序是穩定的一致的。前面我們將select a.* from t_testrownum a order by rvalue執行10次，發現排序是一致的。那 么“SORT ORDER BY STOPKEY”方式的排序是否是一致的呢？我們將語句6執行10次同樣發現，其結果是一致的。那么為什么我們用語句6查第1到10條記錄和11到20條 記錄時，有些記錄為什么在這兩個查詢中出現的名次并不一致呢。
--語句7
select *
from ( select a.* from t_testrownum a order by rvalue )
where rownum <= 20
--語句8
select *
from ( select a.* from t_testrownum a order by rvalue )
where rownum <= 10
為了找出排序不一致的原因，我們分別執行語句7和語句8，這時你會發現，前10名的記錄在兩次查詢中并不一樣。為此，我們得出結論，當 stopkey不同時，排序結果是不同的。為什么會這樣呢，大師TOM的解釋是“SORT ORDER BY STOPKEY”是Oracle為優化TOPN（查詢排序后的前N條記錄）查詢采用的一種算法。大致的思想是：先取出為排序時前面的N條記錄，對這N條記 錄排序，然后用后面的剩下的所有記錄依排序要求插入前N條記錄中。一般來說，這樣的插入排序也應該是穩定的，那為什么N不同，排序結果就不一樣呢？下面的 兩條查詢語句似乎可以給你一點啟發：
--語句9
select a.*, rownum r
from t_testrownum a
whererownum <= 10
orderby rvalue
--語句10
select a.*, rownum r
from t_testrownum a
whererownum <= 20
orderby rvalue
它們的執行計劃如下：
SELECT STATEMENT, GOAL = CHOOSE
SORT ORDER BY
COUNT STOPKEY
TABLE ACCESS FULL Object owner=USDPD502 Object name=T_TESTROWNUM
從上面兩條查詢語句的結果我們可以看出，排在前面的10記錄也不是一致的。要注意的是，這兩條語句的執行計劃中并沒有使用“SORT ORDER BY STOPKEY”算法。而是普通的排序“SORT ORDER BY”。只是這兩次排序的記錄條數不一樣，這時有些人可能會懷疑是在排序前兩次查詢輸出記錄的順序就是不一樣的。我們可以這么測試一下，先對 t_testrownum表的所有20條記錄排序，然后從表中刪除掉后10條記錄，從語句9和語句10中刪除掉whererownum <= N條件，我們發現，查詢結果和語句9和語句10是一樣的。由此，我們可以得出結論，當參與排序的記錄數量不同時，具有相同值的記錄的排序順序是不同的。
進行分頁查詢時，如果同一條記錄在多個分頁中出現，這樣的結果肯定不是你所期望的。為了避免這種現象的發生，一個簡單的方法就是在排序條件中增加輔助排序字段，使得每條記錄的組合排序字段是唯一的。
如何在分頁查詢中避免排序
對于需要排序的分頁查詢來說，如果參與排序的結果集很大，而實際返回的記錄數很少，那么有兩點是需要注意的：第一大結果集排序對系統資源的占用，第 二如果排序字段的值不唯一，某些記錄會出現在多個分頁中。如何避免以上的兩點呢，我們知道，索引的鍵值是有序組織的，我們是否可以利用索引來避免排序呢。 答案是肯定的，我們在rvalue上建立索引：
--語句11
create index idx_testrownum_rvalue on t_testrownum(rvalue);
這時，我們把語句6修改一下：
--語句12
select *
from ( select b.*, rownum r
from ( select a.*
from t_testrownum a
where rvalue > chr(1) order by rvalue ) b
where rownum <= 20 )
where r>= 11
執行語句12，它的執行計劃如下：
SELECT STATEMENT, GOAL = CHOOSE
VIEW Object owner=USDPD502
COUNT STOPKEY
VIEW Object owner=USDPD502
TABLE ACCESS BY INDEX ROWID Object owner=USDPD502 Object name=T_TESTROWNUM
INDEX RANGE SCAN Object owner=USDPD502 Object name=IDX_TESTROWNUM_RVALUE
從上面的執行計劃中，我們已看不到“SORT ORDER BY STOPKEY”字樣，說明沒有排序步驟。那么記錄在分頁中重復的問題是否也解決了呢，經過測試，該問題也不復存在。
那么如果我們需要降序排序呢? 對于降序排序，我們需要增加相應的hints來提示優化器走降序索引掃描。
-語句13
select *
from ( select b.*, rownum r
from ( select/*+index_desc(a idx_testrownum_rvalue)*/a.*
from t_testrownum a
where rvalue > chr(1) order by rvalue desc ) b
where rownum <= 20 )
where r>= 11
語句13的執行計劃如下：
SELECT STATEMENT, GOAL = CHOOSE
VIEW Object owner=USDPD502
COUNT STOPKEY
VIEW Object owner=USDPD502
TABLE ACCESS BY INDEX ROWID Object owner=USDPD502 Object name=T_TESTROWNUM
INDEXRANGE SCAN DESCENDING Objectowner=USDPD502
利用索引避免排序需要的注意點
雖然使用索引來避免排序是一個好方法，但是，任何事物都不可能是十全十美的，使用該方法時需要注意以下幾點：
1) 排序字段上的索引必須是升序索引，如果使用降序索引將導致升序排序時分頁出現問題。（具體是什么原因我現在還沒弄明白，如果有知道原因的可以指點一下）
2) 在分頁開始記錄數大于10000后，利用索引排序進行分頁的性能反而不如直接排序分頁的方式好。（如果字段是數字性，性能下降不大，如果是字符串則性能下降明顯，這可能和字符串和數字的比較方式不同有關）