Oracle ROWNUM 分頁失效根因：ORDER BY 必須唯一

經典分頁寫法：

SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (
    SELECT ... ORDER BY orderseq, biz_delete_time NULLS LAST
  ) t
  WHERE ROWNUM < :end
) WHERE rn >= :start;

實測發現：第 1-3 頁正常，第 4 頁起資料重複。經排查，ORDER BY 欄位組合不唯一 導致 ROWNUM 賦值不穩定。

根因解析

Oracle Ask Tom 明確指出：

「分頁查詢的 ORDER BY 必須排序唯一鍵。若非唯一，必須在末尾補上唯一欄位（如 PK），否則 ROWNUM 分配順序不確定，導致分頁錯亂。」

修正方案

-- 原 ORDER BY orderseq, biz_delete_time NULLS LAST
-- 修正：補上主鍵 eid 保證唯一
ORDER BY orderseq, biz_delete_time NULLS LAST, eid

驗證

修正後重跑分頁，第 4 頁及以後資料不再重複，翻頁順暢。

最佳實踐清單

1. 永遠在 ORDER BY 末尾加上 PK 或唯一鍵，哪怕業務邏輯看起來已唯一。
2. 使用 ROW_NUMBER() OVER (ORDER BY ...) (10g+) 替代 ROWNUM，語意更清晰且支援 PARTITION BY。
3. 12c+ 直接用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY，由優化器保證正確性。
4. 單元測試必須覆蓋「排序鍵重複」場景。

延伸：為何 ROWNUM 敏感於排序唯一性？

ROWNUM 在 WHERE ROWNUM < N 過濾時即分配，若排序不唯一，相同排序鍵的行在不同執行中物理順序可能不同（受平行查詢、統計資訊、資料分布影響），導致被截取的行集合不同。

一個 PK 欄位，解決分頁重複/遺漏的所有疑難雜症。