Oracle 無原生 LIMIT/OFFSET(12c 前),傳統分頁採用 ROWNUM 雙層嵌套。12c 引入 ANSI SQL 標準 OFFSET ... FETCH,語法更直觀。本文對比兩種寫法並說明效能關鍵。
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT person_id, chn_name, chn_firstname_py
FROM t_pbase_info
ORDER BY person_id -- 必須有穩定排序
) a
WHERE ROWNUM <= :end_row -- 例如 20
)
WHERE rn > :start_row; -- 例如 11
ORDER BY 確保結果確定性。ROWNUM <= :end_row 截取前 N 筆。rn > :start_row 過濾掉前 M 筆。ROWNUM 在 ORDER BY 之前產生,故必須用子查詢先排序再取 ROWNUM。SELECT person_id, chn_name, chn_firstname_py FROM t_pbase_info ORDER BY person_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
OFFSET 0 ROWS(相容舊碼)、FETCH FIRST n ROWS ONLY 簡寫。無論哪種寫法,若無合適索引支援 ORDER BY,Oracle 必須 FULL TABLE SCAN → SORT → 分頁,代價隨資料量線性增長。
-- 分頁查詢固定 ORDER BY person_id CREATE INDEX idx_pbase_info_person_id ON t_pbase_info(person_id);
有索引時,Oracle 採用 INDEX RANGE SCAN 直接按序讀取所需區塊,分頁成本與頁碼無關(O(1) 級)。
若 OFFSET 1000000,即使有索引也需掃過 100 萬索引項。解法:
person_id,下頁改用 WHERE person_id > :last_id ORDER BY person_id FETCH FIRST 10。SELECT person_id FROM ... ORDER BY person_id OFFSET ... FETCH ...,再 JOIN 回表取明細欄位。String sql = "SELECT * FROM ("
+ " SELECT a.*, ROWNUM rn FROM ("
+ " SELECT person_id, chn_name FROM t_pbase_info ORDER BY person_id"
+ " ) a WHERE ROWNUM <= ?"
+ ") WHERE rn > ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, endRow); // 20
ps.setInt(2, startRow); // 10
掌握雙層 ROWNUM 與 OFFSET/FETCH 兩種寫法,並配合索引策略解決深分頁,是 Oracle 分頁查詢的標準解決方案。