Oracle 分頁查詢最佳寫法與效能原理解析

Oracle 分頁查詢最佳寫法與效能原理解析

分頁查詢是應用系統核心需求。Oracle 無原生 LIMIT/OFFSET（12c 前），需透過 ROWNUM 機制實作。本文解析兩種主流寫法的效能差異與優化原理。

標準三層嵌套寫法（推薦）

SELECT *
FROM   (SELECT a.*, ROWNUM rn
        FROM   (SELECT * FROM table_name) a
        WHERE  ROWNUM <= 40)
WHERE  rn >= 21;

適用場景：第 2 頁，每頁 20 筆（第 21-40 筆）。

替代寫法（效能較差）

SELECT *
FROM   (SELECT a.*, ROWNUM rn
        FROM   (SELECT * FROM table_name) a)
WHERE  rn BETWEEN 21 AND 40;

效能差異深度解析

關鍵差異：謂詞下推

• 寫法一：中層 WHERE ROWNUM <= 40 可被 CBO 推入最內層查詢。一旦結果集超過 40 筆，內層查詢立即停止（COUNT STOPKEY 操作），避免全表掃描。
• 寫法二：BETWEEN 21 AND 40 位於最外層，無法下推至內層（內層不知 rn 定義）。最內層需產生所有符合條列資料，再由外層過濾，I/O 與記憶體壓力大幅增加。

執行計畫對比

-- 寫法一典型計畫
| Id | Operation                | Name | Rows | Bytes | Cost |
|----|--------------------------|------|------|-------|------|
|  0 | SELECT STATEMENT         |      |   20 |  2000 |   10 |
|* 1 |  VIEW                    |      |   20 |  2000 |   10 |
|* 2 |   COUNT STOPKEY          |      |      |       |      |
|  3 |    VIEW                  |      |   40 |  4000 |   10 |
|  4 |     TABLE ACCESS FULL    | TBL  | 1000 | 10000 |   10 |

-- 寫法二典型計畫
| Id | Operation           | Name | Rows | Bytes | Cost |
|----|---------------------|------|------|-------|------|
|  0 | SELECT STATEMENT    |      |   20 |  2000 |  100 |
|* 1 |  VIEW               |      |   20 |  2000 |  100 |
|* 2 |   FILTER            |      |      |       |      |
|  3 |    VIEW             |      | 1000 | 10000 |  100 |
|  4 |     TABLE ACCESS FULL| TBL  | 1000 | 10000 |  100 |

進階分頁模式

游標分頁（適用於大量資料、頻繁翻頁）

-- 記住上一頁最後一筆的排序鍵值
SELECT * FROM table_name
WHERE  sort_key > :last_sort_key
ORDER  BY sort_key
FETCH FIRST 20 ROWS ONLY;  -- 12c+ 語法

ROW_NUMBER() 分析函數寫法（10g+）

SELECT *
FROM   (SELECT t.*, ROW_NUMBER() OVER (ORDER BY sort_col) rn
        FROM   table_name t)
WHERE  rn BETWEEN 21 AND 40;

最佳實務建議

1. **小資料量/簡單查詢**：三層 ROWNUM 寫法最穩定
2. **大資料量/複雜排序**：優先使用游標分頁或 ROW_NUMBER()
3. **12c+ 環境**：直接使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY 標準語法
4. **索引設計**：確保 ORDER BY 欄位有索引支援，避免 SORT ORDER BY 操作
5. **統計資訊**：定期收集表統計資訊，確保 CBO 正確估算基數

常見誤區

• 使用 ROWNUM 直接配合 ORDER BY（順序錯誤，先取 ROWNUM 再排序）
• 在最外層才過濾 ROWNUM 範圍（失去 STOPKEY 優化）
• 忽略總筆數查詢成本（分頁元件常需總頁數，建議快取或非同步計算）