Oracle ROWNUM 機制深度解析與分頁查詢最佳實踐

ROWNUM 概念核心：偽列的動態分配機制

在 Oracle 資料庫中，ROWNUM 是一個極具迷惑性的偽列。許多開發者誤以為它代表表中的物理行號，實際上它是查詢結果集產生後、動態分配的邏輯序號。理解這一點，是掌握 ROWNUM 行為的關鍵。

基礎驗證：為何 ROWNUM > 10 永遠返回空集？

假設 emp 表有 14 筆資料。執行 SELECT rownum, empno FROM emp WHERE rownum < 10 可正常返回前 9 筆；但若改為 WHERE rownum > 10，卻一筆也查不到。原因在於：

1. Oracle 先取得第一筆符合條件的記錄，分配 ROWNUM = 1。
2. 條件 ROWNUM > 10 判定為假，該記錄被丟棄。
3. 下一筆記錄遞補成為「第一筆」，ROWNUM 重新從 1 開始，依然不滿足 > 10。
4. 循環往複，永無滿足條件之記錄。

常見誤區現象解析

1. ROWNUM != 10 僅返回前 9 筆

第 10 筆記錄的 ROWNUM 為 10，被 != 10 過濾；第 11 筆遞補後 ROWNUM 仍為 10，再被過濾……以此類推，最終只剩前 9 筆。

2. ROWNUM > 1 無結果，ROWNUM >= 1 全返回

同理，首筆 ROWNUM 恆為 1，> 1 導致首筆被捨，後續遞補仍為 1，形成死循環。

3. BETWEEN 2 AND 10 失效

因 ROWNUM 從 1 起算，永無機會達到 2。

正確分頁寫法：子查詢固化 ROWNUM

必須先在內層查詢生成 ROWNUM，外層再篩選：

SELECT *
FROM (
    SELECT t.*, ROWNUM AS rn
    FROM (SELECT * FROM emp ORDER BY empno) t
    WHERE ROWNUM <= 20
)
WHERE rn > 10;

關鍵點：內層 WHERE ROWNUM <= 20 可提前終止掃描，大幅提升效能；外層再過濾 rn > 10 取得第 11-20 筆。

總結

• ROWNUM 是結果集的動態序號，永遠從 1 開始。
• 直接在 WHERE 中使用 >, >=, =, BETWEEN（起始值 > 1）永無結果。
• 分頁必須採用「雙層嵌套 + 別名」模式，且內層限制上限以利用 STOPKEY 優化。