萬盛學電腦網

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

Oracle 分頁查詢標準寫法:ROWNUM 雙層嵌套與 12c OFFSET/FETCH 現代語法

Oracle 無原生 LIMIT/OFFSET(12c 前),傳統分頁採用 ROWNUM 雙層嵌套。12c 引入 ANSI SQL 標準 OFFSET ... FETCH,語法更直觀。本文對比兩種寫法並說明效能關鍵。

傳統寫法(11g 及之前相容)

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。

Oracle 12c+ 標準語法

SELECT person_id, chn_name, chn_firstname_py
FROM t_pbase_info
ORDER BY person_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
  • 語法簡潔、符合 ANSI SQL:2008,可攜性高。
  • 優化器內部仍轉換為類似 ROWNUM 計畫,效能相當。
  • 支援 OFFSET 0 ROWS(相容舊碼)、FETCH FIRST n ROWS ONLY 簡寫。

效能關鍵:避免 Full Scan + Sort

無論哪種寫法,若無合適索引支援 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 萬索引項。解法:

  1. 游標分頁:記住上一頁最後一筆 person_id,下頁改用 WHERE person_id > :last_id ORDER BY person_id FETCH FIRST 10。
  2. 延遲關聯:先只查主鍵 SELECT person_id FROM ... ORDER BY person_id OFFSET ... FETCH ...,再 JOIN 回表取明細欄位。

Java 程式碼片段(傳統寫法)

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 分頁查詢的標準解決方案。

copyright © 萬盛學電腦網 all rights reserved