Oracle 分頁查詢資料重複/遺漏根因與解決方案：排序鍵唯一性

前言

使用 ROWNUM 分頁時，常遇到**跨頁資料重複**或**資料遺漏**現象。根因在於 **排序鍵不唯一** 導致相同排序值的行在分頁邊界處順序不穩定。本文深度剖析機制並給出徹底解決方案。

現象重現

-- 第 1 頁 (1~10)
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (SELECT * FROM t ORDER BY status) a WHERE ROWNUM <= 10
) WHERE rn >= 1;

-- 第 2 頁 (11~20)
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (SELECT * FROM t ORDER BY status) a WHERE ROWNUM <= 20
) WHERE rn >= 11;

若 status 有大量重複值 (如 'A' 佔 80%)，Oracle 排序演算法**不保證穩定性** (非 Stable Sort)，兩次查詢中 status='A' 的行順序可能不同，導致第 10~11 行邊界資料重複或遺漏。

根因分析

1. 無 ORDER BY**：資料依儲存順序 (區塊/槽號) 回傳，任何 DML (INSERT/UPDATE/DELETE/MOVE) 皆可能改變順序 → 分頁極不穩定。
2. ORDER BY 非唯一鍵**：Oracle 排序為 **QuickSort/HeapSort 變體**，不保證相等鍵值的相對順序 (Unstable)。並行查詢 (PX) 更會打亂順序。
3. 分頁邊界落在相同排序值群組中**：第一頁取前 N 行，第二頁取 N+1 行，若第 N 與 N+1 行排序值相同且順序互換 → 重複/遺漏。

徹底解決方案：排序鍵唯一化

原則：ORDER BY 子句**必須包含唯一鍵** (Primary Key 或 Unique Index 欄位) 作為最後一層排序依據。

方案一：附加主鍵 (推薦)

SELECT * FROM (
  SELECT a.*, ROWNUM rn
  FROM (SELECT * FROM t ORDER BY status, id) a  -- id 為 PK
  WHERE ROWNUM <= :max_row
) WHERE rn >= :min_row;

方案二：ROWID 兜底 (無 PK 表)

SELECT * FROM (
  SELECT a.*, ROWNUM rn
  FROM (SELECT * FROM t ORDER BY status, ROWID) a
  WHERE ROWNUM <= :max_row
) WHERE rn >= :min_row;

ROWID 物理唯一，但跨遷移/重組後會變更，**不適合長期分頁游標**，僅作臨時方案。

方案三：決定性排序鍵組合

若業務排序為多欄位 (status, create_date) 仍可能重複，**必須再加 PK**。

ORDER BY status, create_date, id  -- 確保完全唯一

驗證腳本：檢測分頁穩定性

-- 連續執行兩次相同分頁查詢，比較 ID 集合是否完全一致
WITH page1 AS (SELECT id FROM (SELECT id, ROWNUM rn FROM (SELECT id FROM t ORDER BY status, id) WHERE ROWNUM <= 20) WHERE rn >= 11),
     page2 AS (SELECT id FROM (SELECT id, ROWNUM rn FROM (SELECT id FROM t ORDER BY status, id) WHERE ROWNUM <= 20) WHERE rn >= 11)
SELECT CASE WHEN COUNT(*) = 0 THEN '穩定' ELSE '不穩定-有差異' END AS result
FROM (
  SELECT id FROM page1 MINUS SELECT id FROM page2
  UNION ALL
  SELECT id FROM page2 MINUS SELECT id FROM page1
);

最佳實務檢核清單

1. **永遠加 ORDER BY**，且最後一欄為 **PK/UK**。
2. 使用 **綁定變數** 避免硬解析。
3. 大資料量加 /*+ FIRST_ROWS(n) */ Hint 引導 NL + 索引路徑。
4. 12c+ 直接用 OFFSET ... FETCH，語義相同但更清晰。
5. 定期重建表/索引 (ALTER TABLE MOVE, ALTER INDEX REBUILD) 會改變 ROWID，**嚴禁依賴 ROWID 做長期分頁**。