Oracle ROWNUM 陷阱全解析：分頁查詢、條件過濾與 ROWID 差異

精通 ROWNUM 偽欄位機制，避開分頁查詢與條件過濾的經典坑點

ROWNUM 是 Oracle 查詢結果集的行號偽欄位，而非儲存屬性。其「先產生結果集、再分配行號」的特性導致 >、=、BETWEEN 等條件失效。本文系統解析 ROWNUM 行為邏輯、分頁查詢正確寫法、子查詢別名要求、ROWID 差異與實戰避坑清單。

ROWUNM 核心機制

• 查詢執行順序：FROM → WHERE → SELECT → ORDER BY → ROWNUM 分配
• ROWNUM 從 1 開始遞增，永遠存在第 1 行
• WHERE 條件中使用 ROWNUM 時，行號分配發生在 WHERE 過濾之前

經典失效案例與原因

正確生效條件

• WHERE rownum < 10 或 WHERE rownum <= 10：取前 N 行 ✓
• WHERE rownum <= 10 AND rownum > 5：錯誤，同 > 邏輯

分頁查詢標準寫法 (子查詢 + 別名)

-- 取第 11-20 行 (第 2 頁，每頁 10 行)
SELECT *
FROM (
  SELECT rownum AS rn, t.*
  FROM (
    SELECT * FROM large_table WHERE status = 'ACTIVE' ORDER BY created_dt DESC
  ) t
  WHERE rownum <= 20
)
WHERE rn > 10;

三層結構解析

1. 最內層：業務查詢 + 排序 (確定邏輯順序)
2. 中間層：WHERE rownum <= :end_row 截取前 N 行，並 將 rownum 起別名 rn
3. 最外層：WHERE rn > :start_row 過濾目標頁面範圍

關鍵規則：子查詢 ROWNUM 必須起別名

-- 錯誤：外層 WHERE 引用 rownum 會產生新的 rownum (永遠從 1 開始)
SELECT * FROM (SELECT rownum, t.* FROM t) WHERE rownum > 10;  -- 仍無資料

-- 正確：中間層起別名，外層引用別名
SELECT * FROM (SELECT rownum AS rn, t.* FROM t) WHERE rn > 10;

ROWNUM vs ROWID 根本差異

12c+ 原生分頁語法 (OFFSET/FETCH)

SELECT * FROM large_table
WHERE status = 'ACTIVE'
ORDER BY created_dt DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

• 語法簡潔、優化器原生支援、執行計畫更優
• 建議 12c+ 環境優先使用

避坑清單

1. 永遠不要在 WHERE 中直接用 rownum > N、rownum = N、rownum BETWEEN
2. 分頁查詢三層結構：內層排序 → 中層截取+別名 → 外層篩選
3. 中間層 rownum 必須起別名，否則外層引用會產生新 rownum
4. 大量資料分頁：確保排序欄位有索引，避免全表排序
5. 12c+ 優先用 OFFSET/FETCH，舊版用標準三層寫法
6. ROWNUM 不能加表前綴 (t.rownum 錯誤)，ROWID 可以 (t.rowid)

進階應用：TOP-N 查詢優化

-- 取前 10 筆最高薪員工 (索引範圍掃描下推)
SELECT * FROM (
  SELECT * FROM emp ORDER BY sal DESC
)
WHERE rownum <= 10;

Oracle 會將 rownum <= 10 推送到索引範圍掃描層，僅讀取 10 個索引條目，極高效率。

理解 ROWNUM「先過濾、後編號」的本質，是寫出正確高效 Oracle 分頁查詢的關鍵。