Oracle ROWNUM 分頁查詢正確姿勢：先排序再取前 N 筆

Oracle ROWNUM 分頁查詢正確姿勢

常見需求：查詢「最新上架的前 5 筆商品」。直接在 WHERE 條件用 ROWNUM <= 5 再 ORDER BY 是錯誤的——ROWNUM 是在查詢結果產生時即時產生的偽欄位，順序取決於資料存取順序。正確邏輯必須先排序產生有序結果集，再套用 ROWNUM 過濾。

錯誤寫法

-- 錯誤：先過濾 ROWNUM 再排序，實際取到的是「實體存取順序前 5 筆」再排序
SELECT id, name
FROM products
WHERE ROWNUM <= 5
ORDER BY create_time DESC;

正確寫法：內層排序，外層截取

SELECT id, name
FROM (
  SELECT *
  FROM products
  ORDER BY create_time DESC
)
WHERE ROWNUM <= 5;

進階：標準分頁（第 N 頁，每頁 M 筆）

SELECT *
FROM (
  SELECT t.*, ROWNUM rn
  FROM (
    SELECT *
    FROM products
    ORDER BY create_time DESC
  ) t
  WHERE ROWNUM <= :page_size * :page_no
)
WHERE rn > :page_size * (:page_no - 1);

• 最內層：ORDER BY 產生確定順序。
• 中層：賦予 ROWNUM 並截取「截至本頁末筆」的資料。
• 最外層：過濾「本頁起始筆號之後」的資料。

12c+ 更簡潔寫法：OFFSET ... FETCH

SELECT id, name
FROM products
ORDER BY create_time DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

語法標準、可讀性高，建議 12c 以上環境優先使用。

效能提示

• ORDER BY 欄位若有索引，可避免全表排序（Top-N 掃描優化）。
• 深分頁（大 OFFSET）效能下降，考慮 Keyset 分頁（WHERE create_time < last_seen_time）或物化視圖預計算。

小結

記住鐵律：ROWNUM 過濾必須包裹在已排序的子查詢外層。內層定順序，外層切頁面，才能得到業務真正需要的「最新前 N 筆」。