Oracle 分頁查詢最佳實踐：ROWNUM 三層嵌套模式、效能原理解析與 FIRST_ROWS Hint 優化

標準分頁查詢模板 (高效版)

SELECT * FROM (
  SELECT A.*, ROWNUM RN FROM (
    SELECT * FROM table_name  -- 內層：原始查詢 (可含 JOIN、WHERE、ORDER BY)
  ) A
  WHERE ROWNUM <= 40          -- 中層：截取前 N 行
) WHERE RN >= 21;             -- 外層：過濾起始行

查詢第 21-40 行 (每頁 20 行，第 2 頁)。

三層結構效能原理

為何這寫法最快？

1. Predicate Pushdown (謂詞下推)：CBO 將中層 ROWNUM <= :max 推入內層查詢執行計畫
2. Early Termination (提前終止)：內層運算 (掃描/排序/聯結) 一旦產生足夠行數即停止
3. 避免全量物化：不需將完整結果集存入臨時段再過濾

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

-- 低效：中層無 ROWNUM 截斷
SELECT * FROM (
  SELECT A.*, ROWNUM RN FROM (
    SELECT * FROM table_name
  ) A
) WHERE RN BETWEEN 21 AND 40;

缺陷：BETWEEN 在外層，CBO 無法下推至內層 (內層不知 RN 含義)。內層必須產生所有符合條件行，中層賦予 ROWNUM，外層再過濾。大表效能差數數量級。

進階優化：FIRST_ROWS(n) Hint

SELECT /*+ FIRST_ROWS(20) */ * FROM (
  SELECT A.*, ROWNUM RN FROM (
    SELECT * FROM table_name
  ) A
  WHERE ROWNUM <= 40
) WHERE RN >= 21;

• 告知優化器：只需前 20 行最佳回應時間
• 傾向選擇 NESTED LOOPS 而非 HASH JOIN (適合分頁首頁)
• 減少排序面積、臨時表空間使用

含排序查詢的分頁正確姿勢

-- 內層先排序，中層截斷，外層過濾
SELECT * FROM (
  SELECT A.*, ROWNUM RN FROM (
    SELECT * FROM table_name ORDER BY created_date DESC
  ) A
  WHERE ROWNUM <= 40
) WHERE RN >= 21;

關鍵：ORDER BY 必須在內層，確保截斷前已按業務邏輯排序。

多表 JOIN 分頁優化策略

CBO 面對分頁 + JOIN 可能選擇：

• NESTED LOOPS：驅動表取前 N 行 → 探測被驅動表 → 達標即停。分頁首選
• HASH JOIN：必須建立完整雜湊表、掃描全量被驅動表。不適合分頁
• MERGE JOIN：需雙邊排序，效能介於兩者間

強制 NESTED LOOPS：

SELECT /*+ FIRST_ROWS(20) LEADING(t1) USE_NL(t2) */ * FROM (
  SELECT A.*, ROWNUM RN FROM (
    SELECT t1.*, t2.col FROM t1, t2 WHERE t1.id = t2.id ORDER BY t1.created DESC
  ) A
  WHERE ROWNUM <= 40
) WHERE RN >= 21;

12c+ 簡化語法：OFFSET ... FETCH

SELECT * FROM table_name
ORDER BY created_date DESC
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

內部自動轉換為等效 ROWNUM 模式，語法更清晰，效能相同。

分頁效能調優檢查清單

1. ✅ 使用標準三層 ROWNUM 模式 (或 12c OFFSET/FETCH)
2. ✅ 內層包含 ORDER BY (無序分頁無意義)
3. ✅ 加上 /*+ FIRST_ROWS(n) */ Hint (n = 每頁行數)
4. ✅ 驅動表有高選擇性索引支援首頁快速定位
5. ✅ 避免外層再加 ORDER BY (與窗口排序衝突)
6. 🔍 EXPLAIN PLAN 確認出現 COUNT STOPKEY、NESTED LOOPS
7. 📊 監控「首頁」與「深頁」效能差異，深頁可接受較慢

常見誤區

• ❌ 以為 ROWNUM <= 40 在外層也能工作 → 無法下推，全量掃描
• ❌ 使用 ROW_NUMBER() OVER() 分頁 → 需全量排序物化，大表極慢
• ❌ 深頁 (如第 1000 頁) 強求毫秒級 → 分頁本質決定深頁需掃描更多行
• ✅ 深頁優化：游標分頁 / Keyset Pagination (WHERE id > :last_id ORDER BY id LIMIT 20)

Oracle 分頁查詢的精髓在於：三層嵌套 + 中層 STOPKEY + 內層 ORDER BY + FIRST_ROWS Hint。這不只是語法模板，更是對 CBO 謂詞下推與提前終止機制的精準駕馭。掌握此模式，從萬行到億行，首頁查詢皆在毫秒級。