oracle 大資料量分頁查詢

（一）分頁實現及效能

Oracle的分頁查詢語句基本上可以按照本文給出的格式來進行套用。

分頁查詢格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最內層的查詢SELECT * FROM TABLE_NAME表示不進行翻頁的原始查詢語句。ROWNUM <= 40和RN >= 21控制分頁查詢的每頁的範圍。

上面給出的這個分頁查詢語句，在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，將結果儘快的返回。在上面的分頁查詢語句中，這種考慮主要體現在WHERE ROWNUM <= 40這句上。

選擇第21 到40條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過ROWNUM <= 40來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 40語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

對比這兩種寫法，絕大多數的情況下，第一個查詢的效率比第二個高得多。

這是由於CBO 最佳化模式下，Oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對於第一個查詢語句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內層查詢中，這樣Oracle查詢的結果一旦超過了ROWNUM限制條件，就終止查詢將結果返回了。

而第二個查詢語句，由於查詢條件BETWEEN 21 AND 40是存在於查詢的第三層，而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義，因為最內層查詢不知道RN代表什麼）。因此，對 於第二個查詢語句，Oracle最內層返回給中介層的是所有滿足條件的資料，而中介層返回給最外層的也是所有資料。資料的過濾在最外層完成，顯然這個效率 要比第一個查詢低得多。

上面分析的查詢不僅僅是針對單表的簡單查詢，對於最內層查詢是複雜的多表聯集查詢或最內層查詢包含排序的情況一樣有效。

這裡就不對包含排序的查詢進行說明了，下一篇文章會通過例子來詳細說明。下面簡單討論一下多表聯合的情況。對於最常見的等值表串連查詢，CBO 一般可能會採用兩種串連方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會考慮）。在這裡，由於使用了分頁，因此指定了一個返回的最大記錄數，NESTED LOOP在返回記錄數超過最大值時可以馬上停止並將結果返回給中介層，而HASH JOIN必須處理完所有結果集（MERGE JOIN也是）。那麼在大部分的情況下，對於分頁查詢選擇NESTED LOOP作為查詢的串連方法具有較高的效率（分頁查詢的時候絕大部分的情況是查詢前幾頁的資料，越靠後面的頁數訪問幾率越小）。

因此，如果不介意在系統中使用HINT的話，可以將分頁的查詢語句改寫為：

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

(二)Oracle Top n

SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40 

以上是oracle 實現top n的功能

SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM between 2 and 100

總是返回空記錄 

原因：

對於rownum來說它是oracle系統順序分配為從查詢返回的行的編號，返回的第一行分配的是1，第二行是2，依此類推，這個偽欄位可以用於限制查詢返回的總行數，而且rownum不能以任何錶的名稱作為首碼。
 舉例說明：
例如表：student(學生)表，表結構為：
ID　　　    char(6)　　　　　 --學號
name　　　　VARCHAR2(10)　　　--姓名
create table student (ID char(6), name VARCHAR2(100));
insert into sale values('200001',‘張一’);
insert into sale values('200002',‘王二’);
insert into sale values('200003',‘李三’);
insert into sale values('200004',‘趙四’);
commit;
(1) rownum 對於等於某值的查詢條件
如 果希望找到學生表中第一條學生的資訊，可以使用rownum=1作為條件。但是想找到學生表中第二條學生的資訊，使用rownum=2結果查不到資料。因 為rownum都是從1開始，但是1以上的自然數在rownum做等於判斷是時認為都是false條件，所以無法查到rownum = n（n>1的自然數）。
SQL> select rownum,id,name from student where rownum=1;（可以用在限制返回記錄條數的地方，保證不出錯，如：隱式遊標）
SQL> select rownum,id,name from student where rownum=1;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
         1 200001 張一
SQL> select rownum,id,name from student where rownum =2;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
（2）rownum對於大於某值的查詢條件
   如果想找到從第二行記錄以後的記錄，當使用rownum>2是查不出記錄的，原因是由於rownum是一個總是從1開始的偽列，Oracle 認為rownum> n(n>1的自然數)這種條件依舊不成立，所以查不到記錄
SQL> select rownum,id,name from student where rownum >2;
ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
那如何才能找到第二行以後的記錄呀。可以使用以下的子查詢方法來解決。注意子查詢中的rownum必須要有別名，否則還是不會查出記錄來，這是因為rownum不是某個表的列，如果不起別名的話，無法知道rownum是子查詢的列還是主查詢的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;
        NO ID     NAME
---------- ------ ---------------------------------------------------
         3 200003 李三
         4 200004 趙四
SQL> select * from(select rownum,id,name from student)where rownum>2;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
（3）rownum對於小於某值的查詢條件
如果想找到第三條記錄以前的記錄，當使用rownum<3是能得到兩條記錄的。顯然rownum對於rownum<n（(n>1的自然數）的條件認為是成立的，所以可以找到記錄。
SQL> select rownum,id,name from student where rownum <3;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
        1 200001 張一
        2 200002 王二
綜 上幾種情況，可能有時候需要查詢rownum在某區間的資料，那怎麼辦呀從上可以看出rownum對小於某值的查詢條件是人為true的，rownum對 於大於某值的查詢條件直接認為是false的，但是可以間接的讓它轉為認為是true的。那就必須使用子查詢。例如要查詢rownum在第二行到第三行之 間的資料，包括第二行和第三行資料，那麼我們只能寫以下語句，先讓它返回小於等於三的記錄行，然後在主查詢中判斷新的rownum的別名列大於等於二的記 錄行。但是這樣的操作會在大資料集中影響速度。
SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;
        NO ID     NAME
---------- ------ ---------------------------------------------------
         2 200002 王二
         3 200003 李三
（4）rownum和排序
Oracle中的rownum的是在取資料的時候產生的序號，所以想對指定排序的資料去指定的rowmun行資料就必須注意了。
SQL> select rownum ,id,name from student order by name;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
         3 200003 李三
         2 200002 王二
         1 200001 張一
         4 200004 趙四
可以看出，rownum並不是按照name列來產生的序號。系統是按照記錄插入時的順序給記錄排的號，rowid也是順序分配的。為瞭解決這個問題，必須使用子查詢
SQL> select rownum ,id,name from (select * from student order by name);
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
         1 200003 李三
         2 200002 王二
         3 200001 張一
         4 200004 趙四
這樣就成了按name排序，並且用rownum標出正確序號（有小到大）

--- 以上為rownum的必學處，為了更好地使用rownum打下基礎。

其 實, 理解rownum的關鍵是Oracle 如何執行查詢語句. 如果先執行笛卡爾集運算,再執行where條件限制,那麼rownum就可以實現 rownum> n(n>=1)的功能. 但oralce是邊執行笛卡爾集運算,邊應用選擇條件,所以rownum>n(n>1=)永遠不成立 ( 網友評論 )