Oracle 分頁查詢最佳實踐：ROWNUM 經典模式與 12c+ 新特性

Oracle 分頁查詢全解析：從經典三層嵌套到 FETCH FIRST 語法

分頁是應用開發高頻需求。本文梳理 Oracle 歷版本分頁寫法演進，重點講解經典 ROWNUM 三層嵌套原理、ORDER BY 陷阱、12c 引入的標準 OFFSET ... FETCH 語法，並提供性能優化建議。

一、經典三層嵌套模式（Oracle 11g 及之前）

場景：每頁 3 條，查第 2 頁（即第 4-6 條），按 columnName 降序。

SELECT s.*
FROM (
  SELECT rownum AS rn, c.*
  FROM (
    SELECT * FROM tableName ORDER BY columnName DESC
  ) c
  WHERE rownum <= 3 * 2  -- 上限：pageSize * pageNumber
) s
WHERE s.rn >= 3 * (2 - 1) + 1;  -- 下限：pageSize * (pageNumber - 1) + 1

關鍵點解析

1. 最內層：ORDER BY 先排序，生成有序結果集。
2. 中間層：賦予 ROWNUM（從 1 遞增），並截取「上限」行（WHERE rownum <= 上限），利用 ROWNUM 特性提前終止掃描。
3. 最外層：過濾「下限」以上的行，得到目標頁面數據。

二、升序分頁寫法（邏輯對稱）

SELECT *
FROM (
  SELECT rownum AS rn, c.*
  FROM (
    SELECT * FROM tableName ORDER BY columnName ASC
  ) c
  WHERE rownum < 2 * 3 + 1
) s
WHERE s.rn > 3 * (2 - 1);

三、常見陷阱與優化

四、Oracle 12c+ 標準語法（推薦）

SELECT * FROM tableName
ORDER BY columnName DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

• OFFSET n ROWS：跳過前 n 行（對應 (pageNumber-1)*pageSize）。
• FETCH NEXT m ROWS ONLY：取後續 m 行（對應 pageSize）。
• 語法簡潔、符合 ANSI SQL:2008 標準、優化器自動轉換為高效執行計劃。

五、性能優化進階方案

1. 鍵值分頁：記住上一頁最大/最小主鍵值，下一頁用 WHERE id > last_id ORDER BY id FETCH FIRST n ROWS ONLY，避免 OFFSET 掃描。
2. 覆蓋索引：CREATE INDEX idx_table_col ON tableName(columnName) INCLUDE (col1, col2...)（12c+），實現純索引掃描無需回表。
3. 結果集緩存：對熱點分頁查詢使用 RESULT_CACHE Hint 或服務端緩存。
4. 游標分頁：PL/SQL 反饋游標或 JDBC setFetchSize 流式獲取，適合批量導出場景。

六、總結建議

• Oracle 12c 及以上強制使用 OFFSET/FETCH，可讀性與性能雙贏。
• 11g 及以下堅持三層嵌套，切記最內層排序、中間層截取上限、外層過濾下限。
• 大偏移量場景務必採用鍵值分頁或業務邏輯規避深分頁。
• 分頁欄位必須有索引，且盡量使用覆蓋索引消除回表開銷。