Oracle 分頁查詢最佳實踐：ROWNUM 陷阱、WITH 子查詢優化與效能對比

核心挑戰：ROWNUM 的先執行特性

Oracle 的 ROWNUM 在 WHERE/ORDER BY 之前賦值，導致 WHERE ROWNUM BETWEEN 5000 AND 10000 永無結果 — 必須用巢狀子查詢或 ROW_NUMBER() 解決。

經典三層巢狀寫法 (10g 通用)

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM vehicle ORDER BY make
  ) a WHERE ROWNUM <= 10000
) WHERE rn >= 5000;

• 內層排序 → 中層截取前 N 列並賦 ROWNUM → 外層過濾範圍。
• 缺點：語法冗長、最佳化器難以下推 predicate、大偏移量效能急劇下降。

現代標準：ROW_NUMBER() 分析函數 (10g+)

SELECT * FROM (
  SELECT v.*, ROW_NUMBER() OVER (ORDER BY make) AS rn
  FROM vehicle v
) WHERE rn BETWEEN 5000 AND 10000;

• 語義清晰、支援複雜排序、最佳化器可轉換為 WINDOW SORT PUSHED RANK 早期停止排序。
• 12c+ 可搭配 OFFSET ... FETCH (ANSI SQL:2008)：

SELECT * FROM vehicle ORDER BY make
OFFSET 4999 ROWS FETCH NEXT 5000 ROWS ONLY;

高效能場景：WITH 子查詢預先過濾/彙總

當基礎表極大 (千萬列) 時，先在 WITH 中完成過濾、彙總、連接，再對結果集分頁：

WITH filtered AS (
  SELECT /*+ PARALLEL(4) */ registration_no, make, model
  FROM vehicle
  WHERE last_service_date >= DATE '2023-01-01'
    AND category = 'A'
)
SELECT * FROM (
  SELECT f.*, ROW_NUMBER() OVER (ORDER BY make) rn
  FROM filtered f
) WHERE rn BETWEEN 5000 AND 10000;

• 縮小排序/分頁輸入集，大幅降低 Temp 空間與 CPU。
• 可加 Hint (PARALLEL, NO_MERGE) 控制執行計畫。

效能對比實測 (百萬列表、分頁 5000-10000)

索引輔助分頁

• 若 ORDER BY 欄位有索引，最佳化器可執行 INDEX FULL SCAN 避免排序。
• 複合索引覆蓋 WHERE + ORDER BY 可實現 INDEX RANGE SCAN 直接取 Top-N。

避坑清單

1. 不要在外層直接用 ROWNUM BETWEEN ...。
2. 不要對無序集合分頁 (結果不確定)，必須有明確 ORDER BY。
3. 大偏移量 (如第 100 萬頁) 改用 Keyset Pagination (WHERE id > last_seen_id ORDER BY id FETCH FIRST 50)。
4. 統計資訊過期會導致 Cardinality 錯誤，定期 DBMS_STATS.GATHER_TABLE_STATS。

總結

12c+ 首選 OFFSET/FETCH；10g/11g 用 ROW_NUMBER()；超大表務必結合 WITH 預先縮小資料集；極大偏移量改用 Keyset 分頁。