Oracle ROWNUM 偽欄位深度解析：分頁查詢、排序陷阱與效能優化

ROWNUM 是 Oracle 最常被誤解的偽欄位。它不是「資料列的固有編號」，而是「查詢結果集產生順序的流水號」。本文深入解析其運作機制、常見陷阱 (如 ROWNUM > 10 無資料)、正確分頁寫法、排序時機差異，以及效能優化價值。

一、核心機制：ROWNUM 如何產生

執行順序 (關鍵！)：

1. FROM / WHERE 子句執行，產生符合條件的原始列集。
2. 對輸出的每一列，**依序指派 ROWNUM 從 1 起算遞增**。
3. SELECT 列表運算 (含函數、運算式)。
4. GROUP BY / HAVING。
5. ORDER BY 排序 (最後才發生)。

→ ROWNUM 指派發生在 ORDER BY 之前，這是所有誤解的根源。

二、經典陷阱：為何 ROWNUM > 10 永無資料？

SELECT * FROM tbrole WHERE ROWNUM > 10;

邏輯推演：

1. 第一列產生，ROWNUM=1 → 不滿足 > 10 → 捨棄。
2. 第二列產生，ROWNUM **重新從 1 開始** (因第一列被濾掉) → 仍為 1 → 捨棄。
3. 以此類推，永遠無列能達到 ROWNUM > 10。

結論：ROWNUM 只能用於 <、<=、!= (或 BETWEEN 1 AND n)，不能直接用 >、>=、= (大於 1)、BETWEEN m AND n (m>1)。

三、正確分頁查詢寫法 (兩層巢狀)

-- 取第 11-20 筆 (每頁 10 筆)
SELECT *
FROM (
  SELECT r.*, ROWNUM rn
  FROM (
    SELECT * FROM tbrole ORDER BY create_time ASC
  ) r
  WHERE ROWNUM <= 20
)
WHERE rn >= 11;

三層結構解析：

1. 最內層：ORDER BY 先排序，產生有序結果集。
2. 中間層：ROWNUM <= 20 截取前 20 筆，同時賦予 rn 序號。
3. 最外層：rn >= 11 篩選目標頁面區間。

錯誤寫法：將 ORDER BY 放在最外層 — 排序發生在 ROWNUM 指派之後，分頁結果錯亂。

四、12c+ 簡潔語法：OFFSET ... FETCH

SELECT * FROM tbrole
ORDER BY create_time ASC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

標準 SQL:2008 語法，Oracle 12c 完整支援，執行計畫自動優化，強烈建議新專案採用。

五、ROWNUM 的效能優化價值

• 避免大量排序溢寫磁碟：WHERE ROWNUM <= n 讓優化器採用 COUNT STOPKEY 操作，一旦湊齊 n 筆即停止掃描與排序，大幅減少 TEMP 表空間使用與 CPU 消耗。
• 不消除全表掃描：若無索引支援 WHERE 條件，仍需 Full Table Scan，但省去後續排序成本。
• 搭配 FIRST_ROWS(n) Hint 可進一步引導優化器選擇巢狀迴圈、索引存取等優首筆回應的計畫。

六、ROWNUM vs ROWID

七、總結速查

• ✅ WHERE ROWNUM <= 10 / ROWNUM < 10 / ROWNUM BETWEEN 1 AND 10
• ❌ WHERE ROWNUM > 10 / ROWNUM = 5 / ROWNUM BETWEEN 11 AND 20 (直接用)
• 🔧 分頁必用巢狀查詢：內層排序 → 中層 ROWNUM 截斷 → 外層篩選區間
• 🚀 12c+ 直接用 OFFSET ... FETCH
• ⚡ ROWNUM <= n 可觸發 STOPKEY 優化，減少排序 I/O