sql最佳化__rownum的使用【轉】

標籤：

ROWNUM的概念
ROWNUM是一個虛假的列。它將被分配為 1，2，3，4，...N，N 是行的數量。一個ROWNUM值不是被永久的分配給一行 （這是最容易被誤解的）。表中的某一行並沒有標號；你不可以查詢ROWNUM值為5的行——根本沒有這個概念。另一個容易搞糊塗的問題是ROWNUM值是何時被分配的。ROWNUM值的分配是在查詢的謂詞解析之後，任何排序和彙總之前進行的。ROWNUM值只有當被分配之後才會增長。這就是為什麼下面的查詢永遠都不會返回結果：
select * from countries where rownum>1;
ROWNUM > 1對於第一行來說並不是真值，ROWNUM沒有增長到 2。所以，沒有比1大的ROWNUM.永遠都不要使用’ROWNUM>？‘和’ROWNUM=2…N’這樣的條件。

用rownum顯示排序結果的前N條記錄
FROM/WHERE子句先被執行.
根據FROM/WHERE子句輸出的行, ROWNUM被分配給他們並自增長.
SELECT 被應用.
GROUP BY 被應用.
HAVING is 被應用.
ORDER BY 被應用.
這就是為什麼下面的SQL幾乎總是錯誤的:
select rownum,job_title,min_salary
from jobs where rownum<3 order by min_salary;
正確的寫法: 
        select rownum,tmp.* from (
        select job_title,min_salary 
        from jobs order by min_salary) tmp
        where rownum<=3;

用ROWNUM實現分頁
select * from
        (select /*+ FIRST_ROWS(n) */ a.*,
        ROWNUM rnum
        from (your_query_goes_here, with order by) a
        where ROWNUM <=
        :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
FIRST_ROWS(N)使最佳化器考慮最短時間獲得前N條記錄.
:MAX_ROW_TO_FETCH 某一頁中結果集的最後一行。如果你每頁顯示10行，要顯示第6頁,那麼此值取60。
:MIN_ROW_TO_FETCH 某一頁中結果集的第一行。如果你每頁顯示10行，要顯示第6頁,那麼此值取50。

ROWNUM對效能的影響
ROWNUM可以避免oracle在磁碟上進行排序。rownum無法避免全表掃描的發生，但是它可以避免對整個表資料的排序操作，在指定了rownum後，排序操作在記憶體中可以輕鬆完成。

sql最佳化__rownum的使用【轉】