Oracle表資料分頁檢索SQL語句

Oracle表資料分頁檢索SQL語句

關於Oracle資料分頁檢索SQL文法，網路上比比皆是，花樣繁多，本篇也是筆者本人在網路上搜尋的比較有代表性的文法，絕非本人原創，貼在這裡，純粹是為了讓“資料分頁專題系列”看起來稍微完整和豐滿一些，故先在這裡特別聲明一下，以免招來罵聲一片！

先介紹兩個比較有代表性的資料分頁檢索SQL執行個體。
•無ORDER BY排序的寫法。(效率最高)

(經過測試，此方法成本最低，只嵌套一層，速度最快！即使檢索的資料量再大，也幾乎不受影響，速度依然！)

SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
      FROM emp t
      WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
AND TO_DATE ('20060731', 'yyyymmdd')
          AND ROWNUM <= 20) table_alias
WHERE table_alias.rowno >= 10;

•有ORDER BY排序的寫法。(效率最高)

(經過測試，此方法隨著檢索範圍的擴大，速度也會越來越慢哦！)

SELECT *
FROM (SELECT tt.*, ROWNUM AS rowno
      FROM ( SELECT t.*
            FROM emp t
            WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                AND TO_DATE ('20060731', 'yyyymmdd')
                ORDER BY create_time DESC, emp_no) tt
      WHERE ROWNUM <= 20) table_alias
WHERE table_alias.rowno >= 10;

參考以上兩個執行個體，基本上，下面的SQL語句就代表了常規的分頁檢索格式，根據實際需要和個人對SQL的熟練程度，可以自由變換，以得到自己需要的分頁SQL語句。

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最內層返回給中介層的是所有滿足條件的資料，而中介層返回給最外層的也是所有資料。資料的過濾在最外層完成，顯然這個效率要比第一個檢索低得多。

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