Oracle 分頁查詢最佳實踐：ROWNUM 陷阱與高效能分頁寫法

問題背景

Web 應用常需分頁顯示大量查詢結果。資料庫游標無法跨無狀態 HTTP 請求保持，且 Oracle 游標不支援向後捲動。解決方案：由資料庫直接回傳指定範圍資料列。

ROWNUM 運作機制與常見錯誤

ROWNUM 是查詢結果集的遞增序號 (1,2,3...)，在 WHERE 過濾前賦予。因此：

-- 錯誤：永遠回傳 0 筆
SELECT * FROM all_objects WHERE ROWNUM BETWEEN 30 AND 49;
-- 原因：第一列 ROWNUM=1 不符合 30-49 被捨棄，第二列變成新的 1，以此類推

正確寫法：內層限制上限，外層過濾下限

-- 步驟 1：先取前 49 筆 (含 ROWNUM 賦予)
SELECT * FROM all_objects WHERE ROWNUM <= 49;

-- 步驟 2：外層查詢過濾掉前 29 筆
SELECT * FROM (
  SELECT ROWNUM rn, all_objects.* FROM all_objects WHERE ROWNUM <= 49
) WHERE rn >= 30;

使用綁定變數避免硬解析

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

相同 SQL 文字可共用游標，消除重複解析開銷。

存儲過程封裝回傳 REF CURSOR

CREATE OR REPLACE PROCEDURE search(
  p_min INTEGER,
  p_max INTEGER,
  p_rowset OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN p_rowset FOR
    SELECT * FROM (
      SELECT ROWNUM rn, all_objects.* FROM all_objects WHERE ROWNUM <= p_max
    ) WHERE rn >= p_min;
END search;
/

應用層僅接收所需資料列，邏輯清晰分離。

排序與 ROWNUM 互動注意

ROWNUM 在 ORDER BY 之後計算。若需排序分頁：

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

基於規則的優化器 (RBO) 會利用 ROWNUM「短路」提前終止掃描，但 CBO 會依統計資訊決定最佳路徑。

12c+ 新語法：OFFSET / FETCH (參考)

SELECT * FROM all_objects ORDER BY object_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

標準 SQL 語法更直觀，但舊版相容仍需掌握 ROWNUM 寫法。

效能優化建議

• 確保排序欄位有索引，避免全表排序
• 大偏移量 (OFFSET 100000) 仍需掃描前 N 列，考慮 Keyset Pagination (WHERE id > last_seen_id)
• 使用 /*+ FIRST_ROWS(n) */ Hint 引導優化器優先回傳前 N 列