萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

Oracle 分頁查詢技巧:ROWNUM 正確用法與陷阱避坑

核心問題:Oracle 無 LIMIT 子句

MySQL/PostgreSQL 使用 LIMIT offset, count,Oracle 則依賴偽欄位 ROWNUM。但 ROWNUM 在查詢過程中動態分配,導致直接篩選範圍失效。

錯誤寫法

-- ❌ 永遠返回 0 筆
SELECT * FROM employees 
WHERE ROWNUM > 10 AND ROWNUM <= 20;

原因:ROWNUM 從 1 開始遞增,第一行不滿足 > 10 被捨棄,導致計數器永不遞增,後續行永無機會符合條件。

正確寫法:子查詢雙層包裹

-- ✅ 取第 11-20 筆(每頁 10 筆,第 2 頁)
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM employees ORDER BY hire_date DESC
    ) a 
    WHERE ROWNUM <= 20
) 
WHERE rn > 10;

原理:

  1. 最內層排序並產生完整結果集
  2. 中間層賦予 ROWNUM 並截取前 20 筆
  3. 最外層過濾 rn > 10 取得目標頁面

Oracle 12c+ 簡潔語法:OFFSET ... FETCH

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

符合 ANSI SQL 標準,效能與可讀性俱佳,建議優先使用。

效能優化建議

  • 必須加 ORDER BY:無序分頁結果不可預測
  • 索引支援排序欄位:避免全表掃描 + 排序
  • 大偏移量效能劣化:OFFSET 1000000 需掃描前 100 萬行,考慮「游標分頁」(WHERE id > last_seen_id)
  • 統計資訊更新:DBMS_STATS.GATHER_TABLE_STATS 確保執行計畫準確

傳統 MINUS 寫法(不推薦)

SELECT * FROM employees WHERE ROWNUM <= 20
MINUS
SELECT * FROM employees WHERE ROWNUM <= 10;

缺點:執行兩次全查詢、需完整欄位匹配、效能差。

總結對照表

版本推薦語法備註
11g 及之前雙層子查詢 + ROWNUM注意別名衝突
12c+OFFSET ... FETCH標準語法、最佳效能
所有版本游標分頁 (Keyset Pagination)大資料集首選,無深分頁效能問題
copyright © 萬盛學電腦網 all rights reserved