萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 分頁查詢排序導致資料重複問題根因與解決方案 Oracle 分頁查詢排序導致資料重複問題根因與解決方案

問題現象

分頁查詢加上 ORDER BY created_date DESC 後,翻頁時出現大量重複資料。

根因分析

  1. Oracle 分頁查詢 (ROWNUM 或 OFFSET/FETCH) 本身不保證順序,若無 ORDER BY,結果順序不確定。
  2. 加上 ORDER BY created_date DESC 後,排序鍵非唯一 (多筆資料同一天)。
  3. Oracle 排序演算法不穩定:相同鍵值的行在排序前後順序可能改變。
  4. 分頁取第一頁 (ROWNUM<=20) 與第二頁 (ROWNUM>20) 時,相同日期的行可能被分配到不同頁,造成視覺上「重複」或「遺漏」。

解決方案:增加唯一鍵作為第二排序鍵


SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM users u 
    ORDER BY u.created_date DESC, u.id DESC  -- id 為主鍵,唯一
  ) a
  WHERE ROWNUM <= 20
) b
WHERE b.rn >= 0;  -- 第一頁
-- 第二頁: WHERE b.rn > 20 AND b.rn <= 40

12c+ 標準語法 (更簡潔)


SELECT * FROM users
ORDER BY created_date DESC, id DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;  -- 第 1 頁
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY; -- 第 2 頁

效能優化建議

  • 在 (created_date, id) 建立複合索引,支援 INDEX FULL SCAN DESCENDING 避免排序。
  • 大數據量深分頁 (OFFSET 100000) 效能差,改用 Keyset Pagination (Seek Method):
    
    -- 記住上一頁最後一行的 (created_date, id)
    SELECT * FROM users
    WHERE (created_date, id) < (:last_date, :last_id)
    ORDER BY created_date DESC, id DESC
    FETCH FIRST 20 ROWS ONLY;
    
  • 統計資訊及時收集,確保優化器選擇最佳路徑。

總結

分頁查詢必須包含唯一排序鍵 (主鍵或唯一索引欄位),確保排序結果確定性,消除重複/遺漏。這是關係型資料庫分頁的通用鐵律。

copyright © 萬盛學電腦網 all rights reserved