Oracle ROWNUM 分頁查詢完全指南：單行、多行、範圍與排序陷阱

核心原理

ROWNUM 是 Oracle 查詢結果集的**順序編號**，從 1 開始，**在 WHERE 過濾前賦值**，且**早於 ORDER BY 執行**。這導致直接使用 WHERE ROWNUM = N (N>1) 永遠無結果，也解釋了為什麼排序必須在子查詢中完成。

經典用法模板

1. 取第 N 行（N≥1）

-- 正確：子查詢先賦予 ROWNUM 別名
SELECT * FROM (
    SELECT ROWNUM rn, t.* FROM test_tab t
) WHERE rn = 2;

-- 錯誤：直接過濾 ROWNUM = 2 無結果
SELECT * FROM test_tab WHERE ROWNUM = 2;

2. 取前 N 行（Top-N）

SELECT * FROM test_tab WHERE ROWNUM <= 10;

3. 範圍分頁：取第 M 到 N 行（如 4-10 行）

SELECT * FROM (
    SELECT ROWNUM rn, t.* FROM test_tab t WHERE ROWNUM <= 10
) WHERE rn >= 4;

4. 帶條件+排序的分頁（最通用模式）

SELECT * FROM (
    SELECT ROWNUM rn, t.* FROM (
        SELECT * FROM ecode_info
        WHERE a = 1
        ORDER BY ecode DESC
    ) t
) WHERE rn > 19 AND ROWNUM < 20;  -- 取第 20 行

關鍵點：最內層完成過濾與排序，中層賦予 ROWNUM，外層做範圍過濾。

12c+ 現代語法：OFFSET ... FETCH

Oracle 12c 引入 ANSI SQL 標準分頁語法，更簡潔、優化器支持更好：

SELECT * FROM ecode_info
WHERE a = 1
ORDER BY ecode DESC
OFFSET 19 ROWS FETCH NEXT 1 ROW ONLY;  -- 取第 20 行

常見陷阱總結

性能提示

• 大偏移量分頁（如第 10000 頁）效率低，建議使用 Keyset Pagination（記住上一頁最大主鍵，WHERE id > :last_id AND ROWNUM <= N）。
• 確保排序列有索引，避免全表排序後再截取。
• 統計信息準確有助於優化器選擇最優執行計劃。

掌握 ROWNUM 的生成時機與分頁模板，是編寫高效 Oracle 分頁查詢的基礎。