Oracle 限制查詢回傳筆數：ROWNUM 分頁、Top-N 查詢與效能陷阱

核心概念

Oracle 無 LIMIT/TOP 關鍵字，使用虛擬欄位 ROWNUM 達成分頁。關鍵規則：ROWNUM 是在 **WHERE 過濾後、ORDER BY 排序前** 依序分配的流水號（從 1 開始）。

基本用法

取前 N 筆 (Top-N)

SELECT * FROM emp WHERE ROWNUM <= 10;
-- 等同 MySQL: LIMIT 10

錯誤寫法：直接篩選範圍

-- 錯誤：無資料回傳
SELECT * FROM emp WHERE ROWNUM > 10 AND ROWNUM <= 20;
-- 原因：第一列 ROWNUM=1 不符合 >10 被過濾，後續列永無機會獲得 ROWNUM=2...

正確寫法：巢狀查詢 + ROWNUM 篩選

-- 取第 11-20 筆 (頁碼 2, 每頁 10)
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM emp ORDER BY empno  -- 內層先排序
  ) a WHERE ROWNUM <= 20              -- 中層截取前 20
) WHERE rn > 10;                       -- 外層篩選 11-20

標準分頁模板 (Oracle 11g 及以前)

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM large_table
    WHERE filter_col = :val
    ORDER BY sort_col
  ) a WHERE ROWNUM <= :page_size * :page_num
) WHERE rn > :page_size * (:page_num - 1);

Oracle 12c+ 新語法：OFFSET ... FETCH (ANSI SQL 標準)

SELECT * FROM emp
ORDER BY empno
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
-- 等同 LIMIT 10 OFFSET 10

優點：語法清晰、最佳化器可直接轉換為分頁執行計畫、支援 FETCH FIRST n ROWS WITH TIES 處理並列排名。

效能比較與最佳化建議

常見陷阱

1. ORDER BY 在 ROWNUM 之後：必須在內層查詢排序，外層才能保證順序正確。
2. DISTINCT/GROUP BY 與 ROWNUM：會改變列產生順序，導致分頁結果不穩定，建議包在內層完成聚合後再分頁。
3. 雙重 ROWNUM：外層別名 rn、內層 ROWNUM，勿混淆。
4. 綁定變數窺探：分頁參數使用綁定變數時，首次執行計畫可能不適合後續參數值，考慮 OPT_PARAM('_optim_peek_user_binds','FALSE') 或 BIND_AWARE Hint。

實戰範例：高效能分頁查詢

-- 目標：emp 表 100 萬筆，依 hiredate DESC 分頁，每頁 20 筆，取第 50 頁
SELECT * FROM (
  SELECT /*+ FIRST_ROWS(1000) INDEX(e emp_hiredate_idx) */
         e.*, ROWNUM rn
  FROM (
    SELECT * FROM emp e
    WHERE deptno = 10
    ORDER BY hiredate DESC
  ) e
  WHERE ROWNUM <= 1000  -- 50 * 20
) WHERE rn > 980;       -- (50-1)*20

結語

Oracle 分頁核心在於 **「內層排序 → 中層截取上限 → 外層篩選下限」** 三層結構。12c+ 請直接使用 OFFSET FETCH；舊版本務必掌握 ROWNUM 三層巢狀寫法，並善用 FIRST_ROWS(n) Hint 與排序索引，將分頁查詢穩定在毫秒級。