Oracle 分頁查詢優化深度解析：ROWNUM 寫法效能差異與最佳實踐

標準分頁查詢範本

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

最內層為原始查詢，ROWNUM <= 40 控制上限，外層 RN >= 21 控制下限。此寫法在大多數情況效能最優。

兩種寫法的效能本質差異

寫法 A（推薦）：內層限上限，外層限下限

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

寫法 B（不推薦）：外層 BETWEEN 限制雙邊

SELECT * FROM (
    SELECT A.*, ROWNUM RN
    FROM (SELECT * FROM table_name) A
)
WHERE RN BETWEEN 21 AND 40;

為什麼寫法 A 遠優於寫法 B？

在 CBO (Cost-Based Optimizer) 模式下，Oracle 會嘗試將外層查詢條件「下推」至內層以提早過濾：

• 寫法 A：WHERE ROWNUM <= 40 位於第二層，優化器可下推至最內層。查詢滿足 40 筆即停止，避免全表掃描。
• 寫法 B：BETWEEN 21 AND 40 位於第三層，優化器無法下推至最內層（內層不認識 RN 別名）。導致最內層回傳所有符合條件資料，中間層再傳全部，外層才過濾，效能極差。

適用範圍延伸

上述分析不限於單表簡單查詢，對複雜多表聯結、包含排序的查詢同樣有效。

多表聯結時的優化建議

CBO 對等值聯結通常考慮 NESTED LOOPS 與 HASH JOIN。分頁查詢指定最大回傳筆數時：

• NESTED LOOPS：回傳滿筆數即可停止，適合分頁
• HASH JOIN / MERGE JOIN：必須處理完整結果集才能回傳

因此分頁查詢多數情況下 NESTED LOOPS 效能更優（特別是查詢前幾頁）。可用 Hint 強制：

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

補充說明

Oracle 12c+ 可使用標準 OFFSET ... FETCH NEXT ... ROWS ONLY 語法，底層優化邏輯相似，但 ROWNUM 寫法相容性最廣，仍為主流生產環境首選。