Oracle 分頁查詢 ORDER BY 非唯一列導致數據重複/缺失的根因與修正

問題現象

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM users u ORDER BY u.create_date DESC
  ) a WHERE ROWNUM <= 20
) b WHERE b.rn >= 0;

翻頁時出現重複行或數據「跳動」。

根因分析

1. 排序鍵非唯一： create_date 存在大量相同值 (如批量導入同一秒)。
2. Oracle 排序不穩定： 相同排序鍵的行，多次執行順序不保證一致 (取決於塊讀取順序、並行度、統計信息等)。
3. ROWNUM 分頁機制： 先排序再截取 ROWNUM，若排序順序抖動，截取窗口內的行集合就會變化。

標準修正方案

在 ORDER BY 後追加唯一列 (主鍵/唯一索引列)：

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM users u ORDER BY u.create_date DESC, u.user_id DESC
  ) a WHERE ROWNUM <= :page_size * :page_no
) b WHERE b.rn > :page_size * (:page_no - 1);

user_id 為主鍵，保證排序結果確定性。

12c+ 現代語法 (更簡潔、優化器友好)

SELECT * FROM users
ORDER BY create_date DESC, user_id DESC
OFFSET :offset ROWS FETCH NEXT :page_size ROWS ONLY;

• OFFSET/FETCH 為 ANSI SQL 標準，12c+ 原生支持。
• 優化器可直接將其轉換為 WINDOW SORT PUSHED RANK 操作，避免子查詢嵌套。

進階場景：游標分頁 (Keyset Pagination)

超大數據集 (百萬行+) 深分頁時，OFFSET 性能劣化嚴重。改用「記住上一頁最後一行的排序鍵值」：

-- 首頁
SELECT * FROM users
ORDER BY create_date DESC, user_id DESC
FETCH FIRST 20 ROWS ONLY;

-- 後續頁 (假設上一頁最後一行 create_date='2024-01-15 10:00:00', user_id=1005)
SELECT * FROM users
WHERE (create_date, user_id) < (TIMESTAMP '2024-01-15 10:00:00', 1005)
ORDER BY create_date DESC, user_id DESC
FETCH FIRST 20 ROWS ONLY;

優勢：無 OFFSET 掃描開銷，索引範圍掃描極快，適用無限滾動加載。

避坑清單

• 不要在視圖/子查詢中隱式依賴 ROWNUM 排序，必須顯式 ORDER BY。
• 複合唯一鍵 (如 create_date, user_id) 需建立複合索引 支持排序與篩選。
• 分頁參數 (page_no, page_size) 做輸入校驗，防止注入或超大 OFFSET。