Oracle ROW_NUMBER() OVER() 分析函數實現高效分頁查詢

傳統 ROWNUM 分頁存在「先賦號後排序」導致結果錯誤、無法直接跳頁（ROWNUM 必須從 1 開始）等缺陷。Oracle 10g+ 引入的 ROW_NUMBER() OVER() 分析函數完美解決這些問題，成為標準分頁寫法。

ROWNUM 分頁陷阱演示

-- 錯誤：先給 ROWNUM 再 ORDER BY
SELECT ROWNUM r, t.* FROM t_news t WHERE ROWNUM <= 3 ORDER BY t.n_count DESC;
-- 結果：行號亂序，非前 3 大 n_count

-- 正確但笨拙：子查詢排序後再賦 ROWNUM
SELECT ROWNUM r, t.* FROM (SELECT * FROM t_news ORDER BY n_count DESC) t WHERE ROWNUM BETWEEN 1 AND 3;  -- 第 1 頁
-- 第 2 頁需雙層嵌套
SELECT * FROM (SELECT ROWNUM r, t.* FROM (SELECT * FROM t_news ORDER BY n_count DESC) t) WHERE r BETWEEN 4 AND 6;

ROW_NUMBER() OVER() 標準分頁寫法

-- 通用語法
SELECT *
FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY n_count DESC) AS rn
    FROM t_news t
)
WHERE rn BETWEEN :start_row AND :end_row;

實戰範例：每頁 3 筆，依 n_count 降序

-- 建表測試資料
CREATE TABLE t_news (id NUMBER, n_type VARCHAR2(20), n_title VARCHAR2(30), n_count NUMBER);
INSERT INTO t_news VALUES (1,'IT','愛it1',100);
INSERT INTO t_news VALUES (2,'體育','愛體育1',10);
INSERT INTO t_news VALUES (3,'體育','愛體育2',30);
INSERT INTO t_news VALUES (4,'IT','愛it2',300);
INSERT INTO t_news VALUES (5,'IT','愛it3',200);
INSERT INTO t_news VALUES (6,'體育','愛體育3',20);
INSERT INTO t_news VALUES (7,'體育','愛體育4',60);
COMMIT;

-- 第 1 頁 (1-3)
SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY n_count DESC) rn FROM t_news t
) WHERE rn BETWEEN 1 AND 3;
-- 結果: id=4(300), id=5(200), id=1(100)

-- 第 2 頁 (4-6)
SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY n_count DESC) rn FROM t_news t
) WHERE rn BETWEEN 4 AND 6;
-- 結果: id=7(60), id=3(30), id=2(10)

進階：分組分頁（PARTITION BY）

-- 各類別各取前 2 筆
SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY n_type ORDER BY n_count DESC) rn FROM t_news t
) WHERE rn <= 2;

效能優化關鍵點

• 索引支援排序： ORDER BY 欄位建立索引可避免 SORT ORDER BY 操作
• 避免 SELECT *： 僅選取必要欄位，減少投影成本
• 大資料量首頁優化： 12c+ 可用 OFFSET 0 ROWS FETCH FIRST 20 ROWS ONLY，優化器可轉換為 FIRST_ROWS(n) 模式
• 統計資訊準確： 確保 DBMS_STATS 收集包含直方圖，避免 Cardinality 嚴重偏差導致全表掃描

ROWNUM vs ROW_NUMBER() 完整對比

分頁查詢是 Web 應用高頻操作，掌握 ROW_NUMBER() OVER() 不僅寫法優雅，更能配合索引、統計資訊產生最優計劃。舊專案請逐步重構。