Oracle 12c 前無原生 TOP N / LIMIT 語法,傳統方案依賴 ROWNUM 偽列配合內聯視圖。12c 引入標準 SQL:2008 OFFSET ... FETCH FIRST ... ROWS ONLY 語法,簡化分頁與 Top-N 查詢。本文對比兩種方案語法、執行計劃差異、邊界條件處理與效能實測。
-- 獲取按 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. 外層排序:恢復輸出順序 (可選)
優化器識別 WHERE ROWNUM <= N 模式,在執行計劃中出現 COUNT STOPKEY 操作,意味著:
| 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 |
-- 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;
COUNT STOPKEY,效能等同 ROWNUM 模式WITH TIES:包含與第 N 行排序鍵相同的所有行 (如第 5 名薪資 5000,第 6 名也是 5000,則一起返回)OFFSET 組合實現標準分頁| 場景 | ROWNUM 模式 | FETCH FIRST 模式 | 處理建議 |
|---|---|---|---|
| N = 0 | 返回 0 行 (正常) | 返回 0 行 (正常) | 應用層攔截 N<=0 |
| N > 總行數 | 返回所有行 | 返回所有行 | 正常行為 |
| ORDER BY 非唯一鍵 | 結果不確定 (同值行順序隨機) | 同樣不確定,除非用 WITH TIES | ORDER BY 唯一鍵 (如 PK) 作為最後排序鍵 |
| DISTINCT + Top-N | 需三層嵌套 (DISTINCT 在中層) | 直接 SELECT DISTINCT ... FETCH FIRST N | 12c+ 語法更簡潔 |
| UNION ALL 分頁 | 需外層包裹 ROWNUM | 直接在最終結果集 OFFSET/FETCH | 12c+ 明顯優勢 |
| 方案 | 執行時間 | 臨時空間 | 執行計劃關鍵 |
|---|---|---|---|
| ROWNUM 三層嵌套 | 0.42 秒 | 0 MB | SORT ORDER BY STOPKEY |
| FETCH FIRST 100 ROWS ONLY | 0.41 秒 | 0 MB | SORT ORDER BY STOPKEY |
| 無序全表掃描 + 應用層取前 100 | 12.7 秒 | 0 MB | TABLE ACCESS FULL |
結論:兩種語法效能等同,關鍵在於 ORDER BY 列有索引支援 Top-N Sort,否則仍需全表掃描+排序。
-- 每個部門薪資最高的 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 批量處理
OFFSET ... FETCH FIRST ...,標準、可讀、可移植。ORDER BY、中層 ROWNUM <= N、外層可選排序。ROWNUM 直接過濾:WHERE ROWNUM > 10 永無結果,WHERE ROWNUM BETWEEN 11 AND 20 同理。WHERE id > :last_seen_id ORDER BY id FETCH FIRST 10)。