Oracle 分頁查詢標準寫法與效能優化原理深度解析

前言

Oracle 無 LIMIT/OFFSET 語法 (12c 前)，分頁依賴 ROWNUM 偽欄位。本文詳細比較兩種寫法效能差異，解釋 CBO 謂詞下推機制，並給出生產環境建議範本。

標準分頁查詢範本 (高效寫法)

SELECT * FROM (
  SELECT a.*, ROWNUM rn
  FROM (SELECT * FROM your_table WHERE conditions ORDER BY sort_col) a
  WHERE ROWNUM <= :max_row  -- 關鍵：內層限制上限
)
WHERE rn >= :min_row;       -- 外層過濾下限

參數範例：第 3 頁、每頁 20 筆 → :max_row = 60, :min_row = 41。

為什麼這寫法最快？ — 謂詞下推

• CBO 可將 ROWNUM <= :max_row **下推** 至最內層查詢。
• 內層查詢一旦產生足夠行數即**停止** (STOPKEY)，不再繼續掃描/排序。
• 外層 rn >= :min_row 僅在少量結果集上過濾，成本極低。

對比：低效寫法 (BETWEEN 在最外層)

SELECT * FROM (
  SELECT a.*, ROWNUM rn
  FROM (SELECT * FROM your_table WHERE conditions ORDER BY sort_col) a
)
WHERE rn BETWEEN :min_row AND :max_row;  -- 無法下推，內層必須產生所有符合條件的行

• 最內層需完成**完整排序與掃描**，再傳遞給中間層產生 ROWNUM，最後外層過濾。
• 資料量大時，臨時表空間、排序記憶體、I/O 全數暴增。

多表 JOIN 分頁的連接方式選擇

• 分頁隱含「只需前 N 列」 → **Nested Loops (NL)** 為最佳：驅動表取得前 N 列即停止，內表索引查找。
• Hash Join / Sort Merge Join 必須處理**完整結果集** 建立 Hash Table 或排序，再捨棄多餘列，極度浪費。
• 建議加 Hint：/*+ FIRST_ROWS(100) USE_NL(t2) LEADING(t1) */ 強制 NL 並指定驅動表。

12c+ 簡易語法 (OFFSET ... FETCH)

SELECT * FROM your_table
WHERE conditions
ORDER BY sort_col
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

內部仍轉換為 ROWNUM 邏輯，但語法更直觀、可攜性佳 (ANSI SQL:2008)。

生產環境檢核清單

1. **必須有 ORDER BY**，否則分頁結果不穩定 (資料搬移、平行查詢導致順序改變)。
2. ORDER BY 欄位**最好唯一** (含 PK)，否則相同排序鍵值的行順序不定，導致跨頁重複/遺漏 → 解法：ORDER BY sort_col, pk_col。
3. 使用**綁定變數** :max_row, :min_row 避免硬解析，利用游標共用。
4. 大量資料首頁查詢加 /*+ FIRST_ROWS(n) */ Hint 引導優化器選擇 NL + 索引路徑。
5. 監控 V$SQL_WORKAREA_ACTIVE 確認無大量單次排序溢寫磁碟。