Oracle ROWNUM 深度解析：分頁查詢陷阱、子查詢技巧、12c OFFSET FETCH 新標準

ROWNUM 是 Oracle 最常被誤解的偽欄位。本文系統剖析其「從 1 開始、逐行遞增、WHERE 階段過濾」的核心機制，揭示 ROWNUM = n (n>1)、ROWNUM > n 失效根因，提供標準分頁寫法、子查詢技巧，並介紹 12c 引入的 ANSI 標準 OFFSET ... FETCH 寫法。

一、ROWNUM 核心機制

• 查詢結果集產生時，Oracle 為每行依序分配 1, 2, 3...。
• WHERE 子句在 ROWNUM 分配 之前 評估（邏輯上），但實際執行計畫中 ROWNUM 過濾推送至存取路徑。
• 關鍵規則：只有 ROWNUM = 1 或 ROWNUM <= N 能直接在 WHERE 使用；ROWNUM = 2、ROWNUM > 5 永遠回傳 0 列。

二、失效案例演示

-- 建表測試
CREATE TABLE student (id CHAR(6), name VARCHAR2(100));
INSERT INTO student VALUES ('200001', '張一');
INSERT INTO student VALUES ('200002', '王二');
INSERT INTO student VALUES ('200003', '李三');
INSERT INTO student VALUES ('200004', '趙四');
COMMIT;

-- (1) ROWNUM = 1 正常
SELECT rownum, id, name FROM student WHERE rownum = 1;
-- 回傳第一列

-- (2) ROWNUM = 2 失效
SELECT rownum, id, name FROM student WHERE rownum = 2;
-- 回傳 0 列！因為第一列 rownum=1 不滿足 =2 被過濾，第二列永遠沒機會變成 2

-- (3) ROWNUM > 2 失效
SELECT rownum, id, name FROM student WHERE rownum > 2;
-- 回傳 0 列！同理，第一列 rownum=1 不滿足 >2 被過濾，後續列永遠無法獲得 rownum>2

三、正確分頁寫法（11g 及以前）

1. 經典雙層子查詢

SELECT *
FROM (
    SELECT a.*, ROWNUM rn
    FROM (
        SELECT id, name
        FROM   student
        ORDER BY id  -- 必須先排序，再賦予 ROWNUM
    ) a
    WHERE ROWNUM <= :end_row  -- 先截取前 N 列
)
WHERE rn >= :start_row;       -- 再過濾起始列

關鍵點：內層 ORDER BY → 中層 ROWNUM <= end → 外層 rn >= start。別名 rn 必須在中層賦予，外層才能引用。

2. 單層子查詢（較簡潔）

SELECT *
FROM (
    SELECT id, name, ROWNUM rn
    FROM   student
    ORDER BY id
)
WHERE rn BETWEEN :start_row AND :end_row;

同樣依賴「先排序再賦 ROWNUM」的執行順序。

四、12c+ 標準寫法：OFFSET ... FETCH

SELECT id, name
FROM   student
ORDER BY id
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;

• ANSI SQL:2008 標準，可攜性極高。
• 支援 OFFSET 0 ROWS（相容舊邏輯）。
• 可搭配 FETCH FIRST n ROWS ONLY 簡寫。
• 執行計畫顯示 WINDOW NOSORT STOPKEY，效能與雙層子查詢相當。

五、進階陷阱與避坑

六、效能優化建議

1. 索引支援 ORDER BY：分頁查詢的 ORDER BY 欄位務必有索引，避免全表排序。
2. 避免深分頁：OFFSET 1000000 FETCH 10 仍需掃描前 100 萬列；改用「游標分頁」（記住上一頁最大 ID，WHERE id > :last_id ORDER BY id FETCH 10）。
3. 統計資訊準確：CBO 需正確估算行數以選擇最佳存取路徑。
4. 12c+ 優先用 OFFSET FETCH：語義清晰、優化器支援最佳、可攜性強。

ROWNUM 的「先分配後過濾」機制是理解所有分頁查詢行為的鑰匙。掌握雙層子查詢模式與 12c OFFSET FETCH 新標準，並配合索引與游標分頁策略，即可構建高效、正確、可維護的分頁查詢方案。