Oracle 分頁查詢核心技法：ROWNUM 限制結果集與分頁陷阱完整指南

核心差異：Oracle 無 LIMIT，靠 ROWNUM 實現

MySQL/PostgreSQL 使用 LIMIT offset, count，Oracle 則依賴偽欄 ROWNUM。ROWNUM 是查詢結果集產生過程中動態分配的序號（從 1 開始），非儲存於表中。

基本用法：取前 N 筆

SELECT * FROM employees WHERE ROWNUM < 11;  -- 取前 10 筆

優化器可在取得足夠行後提前終止掃描（COUNT STOPKEY 操作），效能極佳。

經典陷阱：無法直接用 RANGE 條件

-- 錯誤寫法：永遠回傳 0 行
SELECT * FROM employees WHERE ROWNUM > 10 AND ROWNUM < 20;

原因：第一行產生時 ROWNUM=1，不滿足 >10 被過濾；後續行 ROWNUM 永遠不會遞增至 2，故無行通過過濾。

正確分頁模式：內層排序 + 外層 ROWNUM 過濾

-- 取第 11~20 筆（按 hire_date 排序）
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM employees ORDER BY hire_date
  ) a WHERE ROWNUM <= 20
) WHERE rn > 10;

三層結構解析

1. 最內層：完成排序與條件過濾，產生有序結果集
2. 中間層：賦予 ROWNUM 並截取前 20 筆（WHERE ROWNUM <= 20）
3. 最外層：過濾 rn > 10，得到第 11~20 筆

12c+ 現代化語法：OFFSET ... FETCH

SELECT * FROM employees 
ORDER BY hire_date 
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle 12c 引入 ANSI SQL 標準分頁語法，碼更易讀、維護，且優化器支援更好，建議新專案優先採用。

效能關鍵：索引支援排序

分頁查詢若無合適索引支援 ORDER BY，資料量大時會觸發全表掃描 + 大量排序。確保排序欄位有索引，或建立複合索引覆蓋查詢欄位，可讓優化器採用 INDEX RANGE SCAN 直接取得有序行，避免排序成本。