在Oracle中用SQL來實現分頁有很多種實現方式但有些語句可能並不是很通用只能用在一些特殊場景之中
以下介紹三種比較通用的實現方案在以下各種實現中ROWNUM是一個最核心的關鍵詞在查詢時他是一個虛擬的列取值為到記錄總數的序號
首先來介紹我們工作中最常使用的一種實現方式
SELECT *
FROM (SELECT ROW_* ROWNUM ROWNUM_
FROM (SELECT *
FROM TABLE
WHERE TABLE_ID = XX
ORDER BY GMT_CREATE DESC) ROW_
WHERE ROWNUM <= )
WHERE ROWNUM_ >= ;
其中最內層的查詢SELECT為不進行翻頁的原始查詢語句可以用自己的任意Select SQL替換ROWNUM <= 和ROWNUM >= 控制分頁查詢的每頁的范圍
分頁的目的就是控制輸出結果集大小將結果盡快的返回上面的SQL語句在大多數情況擁有較高的效率主要體現在WHERE ROWNUM <= 這句上這樣就控制了查詢過程中的最大記錄數
上面例子中展示的在查詢的第二層通過ROWNUM <= 來控制最大值在查詢的最外層控制最小值而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 語句在查詢的最外層控制分頁的最小值和最大值此時SQL語句如下也就是要介紹的第二種實現方式
SELECT *
FROM (SELECT A* ROWNUM RN
FROM (SELECT *
FROM TABLE
WHERE TABLE_ID = XX
ORDER BY GMT_CREATE DESC) A)
WHERE RN BETWEEN AND ;
由於Oracle可以將外層的查詢條件推到內層查詢中以提高內層查詢的執行效率但不能跨越多層
對於第一個查詢語句第二層的查詢條件WHERE ROWNUM <= 就可以被Oracle推入到內層查詢中這樣Oracle查詢的結果一旦超過了ROWNUM限制條件就終止查詢將結果返回了
而 第二個查詢語句由於查詢條件BETWEEN AND 是存在於查詢的第三層而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義因為最內層查詢不知道RN代表什麼）因此對於第二個查詢語句Oracle最內層返回給中間層的是所有滿足條件的數據而中間層返回給最外層的也是所有數據數據的過濾在最外層完成顯然這個效率要比第一個查詢低得多
以上兩種方案完全是通過ROWNUM來完成下面一種則采用ROWID和ROWNUM相結合的方式SQL語句如下
SELECT *
FROM (SELECT RID
FROM (SELECT RRID ROWNUM LINENUM
FROM (SELECT ROWID RID
FROM TABLE
WHERE TABLE_ID = XX
ORDER BY GMT_CREATE DESC) R
WHERE ROWNUM <= )
WHERE LINENUM >= ) T
TABLE T
WHERE TRID = TROWID;
從語句上看共有層Select嵌套查詢最內層為可替換的不分頁原始SQL語句但是他查詢的字段只有ROWID而沒有任何待查詢的實際表字段具體查詢實際字段值是在最外層實現的
這種方式的原理大致為首先通過ROWNUM查詢到分頁之後的條實際返回記錄的ROWID最後通過ROWID將最終返回字段值查詢出來並返回
和前面兩種實現方式相比該SQL的實現方式更加繁瑣通用性也不是非常好因為要將原始的查詢語句分成兩部分（查詢字段在最外層表及其查詢條件在最內層）
但這種實現在特定場景下還是有優勢的比如我們經常要翻頁到很後面比如條記錄中我們經常需要查及其以後的數據此時該方案效率可能要比前面的高
因為前面的方案中是通過ROWNUM <= 來控制的這樣就需要查詢出條數據然後取最後之間的數據而這個方案直接通過ROWID取需要的那條數據
從不斷向後翻頁這個角度來看第一種實現方案的成本會越來越高基本上是線性增長而第三種方案的成本則不會像前者那樣快速他的增長只體現在通過查詢條件讀取ROWID的部分
當然除了以上提了這些方案我們還可以用以下的SQL來實現
SELECT *
FROM TABLE
WHERE TABLE_ID NOT IN
(SELECT TABLE_ID FROM TABLE WHERE ROWNUM <= )
AND ROWNUM <= ;
SELECT *
FROM TABLE
WHERE ROWNUM <=
MINUS
SELECT * FROM TABLE WHERE ROWNUM <= ;
………………
注意當ROWNUM作為查詢條件時他是在order by之前執行所以要特別小心
比如我們想查詢TABLE中按TABLE_ID倒序排列的前條記錄不能用如下的SQL來完成
SELECT * FROM TABLE WHERE ROWNUM <= ORDER BY TABLE_ID DESC;
From:http://tw.wingwit.com/Article/program/Oracle/201311/11198.html
