Oracle 分頁查詢最佳實踐：ROWNUM 多層嵌套與 WITH 子句

分頁核心原理

Oracle 無 LIMIT/OFFSET，需利用 ROWNUM 偽欄位配合子查詢實現。關鍵在於 先限制上限再過濾下限，避免全表掃描。

無 ORDER BY：最高效單層嵌套 (推薦方案 1)

SELECT *
FROM (
    SELECT ROWNUM rn, t.*
    FROM k_task t
    WHERE flight_date BETWEEN DATE '2006-05-01' AND DATE '2006-07-31'
      AND ROWNUM <= 20          -- 關鍵：內層先截取前 20 筆
) WHERE rn >= 10;              -- 外層再過濾第 10-20 筆

優點：只掃描必要資料量，效能不隨資料量增長而劣化。

有 ORDER BY：雙層嵌套 (推薦方案 2)

SELECT *
FROM (
    SELECT tt.*, ROWNUM rn
    FROM (
        SELECT t.*
        FROM k_task t
        WHERE flight_date BETWEEN DATE '2006-05-01' AND DATE '2006-05-31'
        ORDER BY fact_up_time, flight_no
    ) tt
    WHERE ROWNUM <= 20
) WHERE rn >= 10;

注意：排序會導致全範圍掃描，資料量大時效能下降。

替代寫法：BETWEEN 過濾 (方案 3/4，不推薦)

-- 無 ORDER BY
SELECT * FROM (
    SELECT ROWNUM rn, t.* FROM k_task t
    WHERE flight_date BETWEEN ... 
) WHERE rn BETWEEN 10 AND 20;

-- 有 ORDER BY
SELECT * FROM (
    SELECT tt.*, ROWNUM rn FROM (
        SELECT t.* FROM k_task t
        WHERE ... ORDER BY fact_up_time, flight_no
    ) tt
) WHERE rn BETWEEN 10 AND 20;

缺點：內層無 ROWNUM <= 上限 限制，需掃描全部符合條件資料。

WITH 子句 (CTE) 寫法 (方案 5/6)

-- 有 ORDER BY
WITH partdata AS (
    SELECT ROWNUM rn, tt.*
    FROM (
        SELECT t.* FROM k_task t
        WHERE flight_date BETWEEN ... ORDER BY fact_up_time, flight_no
    ) tt
    WHERE ROWNUM <= 20
)
SELECT * FROM partdata WHERE rn >= 10;

-- 無 ORDER BY
WITH partdata AS (
    SELECT ROWNUM rn, t.*
    FROM k_task t
    WHERE flight_date BETWEEN ... AND ROWNUM <= 20
)
SELECT * FROM partdata WHERE rn >= 10;

優點：語義清晰，易維護；效能與對應嵌套寫法相同。

效能總結與建議

避坑指南：永遠不要在最外層直接用 ROWNUM BETWEEN 而省略內層上限限制，否則大資料量下會全表掃描導致效能災難。