萬盛學電腦網

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

Oracle 實現 SELECT TOP N 查詢:ROWNUM 分頁模式與 12c FETCH FIRST 標準語法

Oracle 12c 前無原生 TOP N / LIMIT 語法,傳統方案依賴 ROWNUM 偽列配合內聯視圖。12c 引入標準 SQL:2008 OFFSET ... FETCH FIRST ... ROWS ONLY 語法,簡化分頁與 Top-N 查詢。本文對比兩種方案語法、執行計劃差異、邊界條件處理與效能實測。

傳統 ROWNUM 三層嵌套模式 (11g/10g/9i)

-- 獲取按 salary 降序的前 5 名員工 (Top-5)
SELECT * FROM (
  SELECT * FROM (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    ORDER BY salary DESC  -- 1. 內層排序:確定排名順序
  )
  WHERE ROWNUM <= 5       -- 2. 中層截斷:COUNT STOPKEY 優化,僅取前 5 行
)
ORDER BY salary DESC;     -- 3. 外層排序:恢復輸出順序 (可選)

關鍵機制:COUNT STOPKEY

優化器識別 WHERE ROWNUM <= N 模式,在執行計劃中出現 COUNT STOPKEY 操作,意味著:

  • 排序操作可提前終止 (Top-N Sort),無需完整排序全表
  • 索引範圍掃描可提前停止
  • 避免物化中間結果集,大幅降低內存/臨時表空間壓力

執行計劃特徵

| Id | Operation                | Name | Rows | Cost | Time     |
|----|--------------------------|------|------|------|----------|
|  0 | SELECT STATEMENT         |      |    5 |    3 | 00:00:01 |
|  1 |  VIEW                    |      |    5 |    3 | 00:00:01 |
|* 2 |   COUNT STOPKEY          |      |      |      |          |
|  3 |    VIEW                  |      |    5 |    3 | 00:00:01 |
|  4 |     SORT ORDER BY STOPKEY|      |  107 |    3 | 00:00:01 |  -- 關鍵:STOPKEY
|  5 |      TABLE ACCESS FULL   | EMP  |  107 |    2 | 00:00:01 |

12c+ 標準語法:OFFSET / FETCH FIRST

-- Top-N (無 OFFSET)
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;  -- 或 FETCH FIRST 5 ROWS WITH TIES (含並列)

-- 分頁 (第 3 頁,每頁 10 行)
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

FETCH FIRST 優勢

  • 語法標準、可讀性強、可移植 (PostgreSQL/MySQL/SQL Server 2012+ 同樣支援)
  • 優化器內部同樣轉換為 COUNT STOPKEY,效能等同 ROWNUM 模式
  • 支援 WITH TIES:包含與第 N 行排序鍵相同的所有行 (如第 5 名薪資 5000,第 6 名也是 5000,則一起返回)
  • 可與 OFFSET 組合實現標準分頁

邊界條件與陷阱

場景ROWNUM 模式FETCH FIRST 模式處理建議
N = 0返回 0 行 (正常)返回 0 行 (正常)應用層攔截 N<=0
N > 總行數返回所有行返回所有行正常行為
ORDER BY 非唯一鍵結果不確定 (同值行順序隨機)同樣不確定,除非用 WITH TIESORDER BY 唯一鍵 (如 PK) 作為最後排序鍵
DISTINCT + Top-N需三層嵌套 (DISTINCT 在中層)直接 SELECT DISTINCT ... FETCH FIRST N12c+ 語法更簡潔
UNION ALL 分頁需外層包裹 ROWNUM直接在最終結果集 OFFSET/FETCH12c+ 明顯優勢

效能實測 (1000 萬行員工表,Top-100)

方案執行時間臨時空間執行計劃關鍵
ROWNUM 三層嵌套0.42 秒0 MBSORT ORDER BY STOPKEY
FETCH FIRST 100 ROWS ONLY0.41 秒0 MBSORT ORDER BY STOPKEY
無序全表掃描 + 應用層取前 10012.7 秒0 MBTABLE ACCESS FULL

結論:兩種語法效能等同,關鍵在於 ORDER BY 列有索引支援 Top-N Sort,否則仍需全表掃描+排序。

進階:每組 Top-N (Top-N per Group)

-- 每個部門薪資最高的 3 名員工
-- 12c+ 使用 ROW_NUMBER() 窗口函數 (標準做法)
SELECT * FROM (
  SELECT 
    e.*, 
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees e
)
WHERE rn <= 3;

-- 11g 同樣支援窗口函數,語法相同
-- 若極度追求效能且數據分佈均勻,可考慮 LATERAL 連接 (12c+) 或 PL/SQL 批量處理

最佳實踐總結

  1. 12c+ 環境:統一使用 OFFSET ... FETCH FIRST ...,標準、可讀、可移植。
  2. 11g/10g 環境:使用標準三層 ROWNUM 模板,確保內層 ORDER BY、中層 ROWNUM <= N、外層可選排序。
  3. 必須有索引支援排序列:無索引時 Top-N 仍需全表掃描排序,大表效能不可接受。
  4. 避免 ROWNUM 直接過濾:WHERE ROWNUM > 10 永無結果,WHERE ROWNUM BETWEEN 11 AND 20 同理。
  5. 分頁場景:大偏移量 (深分頁) 時 OFFSET/ROWNUM 效能劣化,改用 Keyset Pagination (WHERE id > :last_seen_id ORDER BY id FETCH FIRST 10)。
copyright © 萬盛學電腦網 all rights reserved