Oracle 分頁查詢的多種實現方式總結

概述

Oracle 資料庫本身不支援類似 MySQL LIMIT 或 SQL Server TOP 的簡易分頁語法，需透過子查詢與 ROWNUM、ROW_NUMBER() 分析函數或 OFFSET ... FETCH (12c+) 來實現。本文整理常見的分頁寫法並比較效能。

方案一：傳統 ROWNUM 兩層嵌套 (適用於 10g 及以上)

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

優點：相容性好，所有版本通用。
缺點：需掃描前 :end_row 筆資料，大翻頁效能遞減。

方案二：ROW_NUMBER() 分析函數 (10g+ 推薦)

SELECT * FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY id) rn
  FROM your_table t
) WHERE rn BETWEEN :start_row AND :end_row;

優點：語法清晰，優化器可選擇更好執行計畫。
缺點：仍需排序全表再過濾。

方案三：OFFSET ... FETCH NEXT (12c 以上原生支援)

SELECT * FROM your_table
ORDER BY id
OFFSET :offset ROWS FETCH NEXT :page_size ROWS ONLY;

優點：語法簡潔，符合 ANSI SQL 標準，優化器專門優化。
缺點：僅 12c 及以上版本可用。

方案四：鍵值分頁 (Keyset Pagination) — 大數據量首選

-- 首頁
SELECT * FROM your_table WHERE id > 0 ORDER BY id FETCH FIRST 20 ROWS ONLY;
-- 後續頁 (假設上一頁最大 id = 100)
SELECT * FROM your_table WHERE id > 100 ORDER BY id FETCH FIRST 20 ROWS ONLY;

優點：無需掃描前置資料，效能極佳，適合無限滾動/大量資料。
缺點：不支援隨機跳頁，需應用層記錄上一頁邊界值。

效能比較與建議

常見陷阱提醒

• ROWNUM 在 ORDER BY 之前賦值，務必用子查詢先排序再過濾 ROWNUM。
• 分頁查詢必須帶 ORDER BY，否則結果順序不穩定。
• 避免在大表上使用 OFFSET 1000000 ROWS，改用 Keyset 分頁。