分頁查詢加上 ORDER BY created_date DESC 後,翻頁時出現大量重複資料。
ROWNUM 或 OFFSET/FETCH) 本身不保證順序,若無 ORDER BY,結果順序不確定。ORDER BY created_date DESC 後,排序鍵非唯一 (多筆資料同一天)。
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
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 避免排序。
-- 記住上一頁最後一行的 (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;
分頁查詢必須包含唯一排序鍵 (主鍵或唯一索引欄位),確保排序結果確定性,消除重複/遺漏。這是關係型資料庫分頁的通用鐵律。