Oracle Top-N 查詢與分頁三大經典模式：ROWNUM 實戰詳解

背景：Oracle 無 SELECT TOP

Oracle 不支援 SELECT TOP N，傳統方案結合 ORDER BY + ROWNUM 實現。關鍵在於 ROWNUM 在 ORDER BY 之前產生，需用子查詢先排序再過濾。

模式一：基本 Top-N

SELECT * FROM (
  SELECT * FROM customer ORDER BY name
) WHERE ROWNUM <= 3;

執行順序：內層全表排序 → 外層截取前 3 筆 → 可選外層 ORDER BY ROWNUM 保證輸出順序。

範例結果：eighth, fifth, first（按 name 字母序）。

模式二：Top-N 中取第 M 筆（M ≤ N）

SELECT * FROM (
  SELECT ROWNUM rn, id, name FROM (
    SELECT * FROM customer ORDER BY name
  ) WHERE ROWNUM <= 3
) WHERE rn = 2;

• 內層同模式一，截取前 N 筆並附加 ROWNUM 別名 rn。
• 外層依 rn 精準取第 M 筆。

結果：fifth（第 2 筆）。

模式三：直接取排序後第 N 筆（M = N）

SELECT * FROM (
  SELECT ROWNUM rn, id, name FROM (
    SELECT * FROM customer ORDER BY name
  ) WHERE ROWNUM <= 2
) WHERE rn = 2;

內層 ROWNUM <= N 即可，省去多讀資料。結果同模式二。

模式四：分頁查詢——取第 M 筆起 X 筆

SELECT * FROM (
  SELECT ROWNUM rn, id, name FROM (
    SELECT * FROM customer ORDER BY name
  ) WHERE ROWNUM <= :M + :X - 1
) WHERE rn BETWEEN :M AND :M + :X - 1;

• :M 起始筆數（1-based），:X 頁大小。
• 內層上限 M+X-1 確保覆蓋所需範圍。
• 外層 BETWEEN 精準切片。

範例：M=2, X=3 → 取第 2~4 筆 → fifth, first, forth。

進階：封裝為分頁預存程序

CREATE OR REPLACE PROCEDURE page_query(
  p_start IN NUMBER,  -- 起始筆號
  p_size  IN NUMBER,  -- 頁大小
  p_refcur OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_refcur FOR
  SELECT * FROM (
    SELECT ROWNUM rn, c.* FROM (
      SELECT * FROM customer ORDER BY name
    ) c WHERE ROWNUM <= p_start + p_size - 1
  ) WHERE rn >= p_start;
END;

應用端呼叫即可實現通用分頁，避免 SQL 注入與重複代碼。

12c+ 新語法：OFFSET ... FETCH

SELECT * FROM customer ORDER BY name
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY;  -- 等同 M=2, X=3

語法簡潔、最佳化器支援更好，優先採用（需相容性參數 COMPATIBLE=12.1.0 以上）。

效能提示

• 大資料量深分頁（如第 10000 頁）ROWNUM 方案需掃描大量資料，效能劣化。
• 改用 Keyset Pagination (Seek Method)：WHERE id > :last_id ORDER BY id FETCH FIRST :size，配合索引極快。
• 確保 ORDER BY 欄位有索引，避免全表排序。

結語

掌握 ROWNUM 三層巢狀結構（排序→截取→切片）是 Oracle 分頁基石。12c+ 環境請直接使用標準 OFFSET/FETCH，舊版本封裝預存程序統一介面，並評估 Keyset Pagination 解決深分頁效能難題。