Oracle 分頁與排序查詢完全指南：ROWNUM 三層巢狀寫法、OFFSET/FETCH (12c+) 與效能優化

Oracle 分頁查詢核心機制：ROWNUM vs OFFSET/FETCH

Oracle 無 LIMIT / TOP，傳統依賴 ROWNUM 偽欄位實現分頁。12c 起支援標準 SQL OFFSET ... FETCH。本文詳細對比兩種寫法、排序結合分頁的正確姿勢、效能陷阱與優化建議。

基礎分頁查詢 (無排序)

-- 前 10 筆
SELECT * FROM TEST_TABLE WHERE ROWNUM <= 10;

-- 第 11-20 筆 (經典三層寫法)
SELECT * FROM (
    SELECT T.*, ROWNUM RN FROM TEST_TABLE T WHERE ROWNUM <= 20
) WHERE RN > 10;

排序後分頁 (關鍵：排序必須在最內層)

-- 按 NAME 升序，前 10 筆
SELECT * FROM (
    SELECT * FROM TEST_TABLE ORDER BY NAME ASC
) WHERE ROWNUM <= 10;

-- 按 NAME 升序，第 11-20 筆
SELECT * FROM (
    SELECT TT.*, ROWNUM RN FROM (
        SELECT * FROM TEST_TABLE ORDER BY NAME ASC
    ) TT WHERE ROWNUM <= 20
) WHERE RN > 10;

原理：ROWNUM 在 WHERE 階段產生，若先 WHERE ROWNUM <= N 再 ORDER BY，會先取前 N 筆未排序資料再排序 → 結果錯誤。必須 最內層排序 → 中層截取上限 → 外層過濾下限。

Oracle 12c+ 標準語法 (OFFSET / FETCH)

-- 前 10 筆
SELECT * FROM TEST_TABLE ORDER BY NAME ASC FETCH FIRST 10 ROWS ONLY;

-- 第 11-20 筆
SELECT * FROM TEST_TABLE ORDER BY NAME ASC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- 參數化
SELECT * FROM TEST_TABLE ORDER BY NAME ASC OFFSET :offset ROWS FETCH NEXT :pagesize ROWS ONLY;

• 語法簡潔、可讀性高、符合 ANSI SQL 標準
• 優化器內部仍轉換為類似 ROWNUM 計畫，效能相當
• 支援 FETCH FIRST N ROWS WITH TIES 處理並列排名

效能優化關鍵點

常見錯誤與修正

1. 排序在外層：

   
   -- 錯誤：先取 20 筆再排序
   SELECT * FROM (SELECT * FROM T WHERE ROWNUM <= 20) ORDER BY NAME;
   -- 正確：最內層排序
   SELECT * FROM (SELECT * FROM T ORDER BY NAME) WHERE ROWNUM <= 20;
   

2. ROWNUM 起始值誤解：ROWNUM 從 1 開始，無法直接 WHERE ROWNUM BETWEEN 11 AND 20 (永無結果)，必須三層巢狀。
3. DISTINCT + ROWNUM：SELECT DISTINCT ... WHERE ROWNUM <= N 可能回傳 < N 筆，因為 DISTINCT 在 ROWNUM 之後執行。解法：最內層 SELECT DISTINCT ... ORDER BY ...。

封裝通用分頁函數 (PL/SQL)

CREATE OR REPLACE FUNCTION PAGE_QUERY(
    P_SQL      IN VARCHAR2,      -- 含 ORDER BY 的基礎查詢
    P_PAGE     IN NUMBER,        -- 第幾頁 (1-based)
    P_SIZE     IN NUMBER         -- 頁大小
) RETURN SYS_REFCURSOR IS
    V_CUR   SYS_REFCURSOR;
    V_START NUMBER := (P_PAGE - 1) * P_SIZE + 1;
    V_END   NUMBER := P_PAGE * P_SIZE;
    V_FINAL VARCHAR2(4000);
BEGIN
    V_FINAL := 'SELECT * FROM (
                    SELECT T.*, ROWNUM RN FROM (' || P_SQL || ') T
                    WHERE ROWNUM <= ' || V_END || '
                ) WHERE RN >= ' || V_START;
    OPEN V_CUR FOR V_FINAL;
    RETURN V_CUR;
END;
/

總結建議

• Oracle 12c+ 專案：優先使用 OFFSET/FETCH，程式碼可維護性最高
• 10g/11g 維護專案：掌握 三層 ROWNUM 巢狀結構，記住「最內層排序」鐵律
• 深分頁場景：棄用 OFFSET/ROWNUM，改用 Keyset/Cursor Pagination (記住上一頁最大值)
• 永遠對分頁查詢加 ORDER BY，否則結果順序不確定