Oracle ROWNUM 偽欄位特性與分頁查詢陷阱完整解析

前言

ROWNUM 是 Oracle 最獨特的偽欄位，**在行被篩選/輸出時才產生**，而非儲存於資料塊。這特性導致許多直觀寫法失效，本文詳細解析運作機制與正確用法。

核心特性：行號隨篩選動態產生

• 查詢開始 → 從資料源逐行讀取 → **滿足 WHERE 條件** → 分配下一個 ROWNUM (1,2,3...)。
• 若第一行就被 WHERE 條件過濾掉，**該行永不獲得 ROWNUM**，下一行若符合條件則獲得 ROWNUM=1。

經典失效案例

-- 錯誤：試圖直接取第 30~49 行
SELECT * FROM all_objects WHERE ROWNUM BETWEEN 30 AND 49;
-- 結果：永遠回傳 0 列

**原因**：第一行讀取時 ROWNUM=1，不符合 BETWEEN 30 AND 49 被丟棄；第二行讀取時 ROWNUM 仍為 1 (因前一行未計號)，同樣被丟棄 → 所有行皆被丟棄。

正確分頁模式：子查詢 + 別名

-- 步驟 1：先取前 49 行 (獲得 ROWNUM 1~49)
SELECT * FROM all_objects WHERE ROWNUM <= 49;

-- 步驟 2：包裝為子查詢，給 ROWNUM 別名，再過濾下限
SELECT * FROM (
  SELECT ROWNUM rn, a.* FROM all_objects a WHERE ROWNUM <= 49
) WHERE rn >= 30;

進階：綁定變數避免硬解析

SELECT * FROM (
  SELECT ROWNUM rn, a.* FROM all_objects a WHERE ROWNUM <= :max_row
) WHERE rn >= :min_row;

使用 :max_row, :min_row 綁定變數，相同 SQL 文本可共用游標，減少 Library Cache 爭用。

關鍵細節：ROWNUM 與 ORDER BY 執行順序

1. FROM / WHERE / SELECT / ROWNUM 分配
2. ORDER BY 排序

→ 先分配 ROWNUM **再排序**，故 WHERE ROWNUM <= 10 ORDER BY created_date 會先取任意 10 列再排序，**非最新 10 筆**。

正確「排序後分頁」寫法

SELECT * FROM (
  SELECT a.*, ROWNUM rn
  FROM (SELECT * FROM all_objects ORDER BY created_date DESC) a
  WHERE ROWNUM <= :max_row
) WHERE rn >= :min_row;

最內層先排序，中間層分配 ROWNUM 並截斷，外層過濾下限。

規則基礎優化器 (RBO) 的特例

舊版 RBO 會利用 ROWNUM 進行「短路」：一旦滿足 ROWNUM 限制即停止查詢並回傳，不再處理剩餘資料。CBO 也有類似 **STOPKEY** 最佳化 (見 分頁查詢篇)。

總結口訣

「ROWNUM 隨行生、先篩後號、無法直接取中間、子查詢加別名、排序要最內、綁定變數共游標」