Oracle ROWNUM 分頁查詢陷阱與正確寫法詳解

ROWNUM 基礎行為

• ROWNUM 是查詢結果集生成時的**順序號**，從 1 開始遞增。
• WHERE ROWNUM < N 可取前 N-1 行；WHERE ROWNUM > N **永遠返回空集**，因為第一行 ROWNUM=1 不滿足 >N，導致後續行永無機會獲得 ROWNUM=2。
• ROWNUM 賦值發生在 **WHERE/ORDER BY 之前**，因此 WHERE ROWNUM < 4 ORDER BY col 先取前 3 行再排序，非預期的「排序後取前 3」。

驗證 ROWNUM 與 ROWID 關係

SELECT t.*, ROWNUM, ROWID FROM table1 t;
-- 發現 ROWNUM 按 ROWID（插入順序）遞增，與業務欄位無關。

正確的 Top-N 查詢（排序後取前 N）

SELECT * FROM (
  SELECT * FROM table1 ORDER BY aaa
) WHERE ROWNUM < 4;

內層先排序，外層再截取 ROWNUM。

標準分頁寫法（取第 M+1 到 M+N 行）

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM table1 ORDER BY aaa
  ) a WHERE ROWNUM <= :end_row
) WHERE rn > :start_row;

關鍵點：

1. 最內層排序。
2. 中層賦予 ROWNUM rn 並限制上界 :end_row。
3. 最外層按 rn 過濾下界 :start_row。

此寫法在 10g 及之前版本通用；12c+ 可直接使用 OFFSET ... FETCH NEXT ... 標準語法。