Oracle 無原生 LIMIT/OFFSET (12c 前),分頁查詢依賴 ROWNUM 偽列特性。同時,NOT IN 子查詢在大數據量下效能災難性,外連接 + IS NULL 是經典替代模式。本文提供標準分頁模板、ROWNUM 陷阱解析、外連接改寫原理與效能對比實測。
分頁查詢:標準三層嵌套模板 (11g/12c-)
-- 獲取第 11-20 行 (每頁 10 行,第 2 頁)
SELECT * FROM (
SELECT a.*, ROWNUM rn
FROM (
SELECT *
FROM t_pbase_info
ORDER BY person_id -- 必須有確定排序,否則結果不確定
) a
WHERE ROWNUM <= 20 -- endRow = pageNo * pageSize
)
WHERE rn > 10; -- startRow = (pageNo-1) * pageSize
關鍵點解析
- 內層排序:
ORDER BY 必須在最內層,確定行序。
- 中層 ROWNUM 截斷:
WHERE ROWNUM <= endRow 限制輸出行數,優化器可推導 COUNT STOPKEY 操作,提前終止掃描。
- 外層過濾起始行:
WHERE rn > startRow 過濾前 N 行。
- 別名衝突:外層別名
rn 避免與內層 ROWNUM 衝突。
12c+ 簡化語法 (OFFSET/FETCH)
SELECT *
FROM t_pbase_info
ORDER BY person_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; -- 標準 SQL:2008
ROWNUM 經典陷阱
| 錯誤寫法 | 現象 | 原因 |
WHERE ROWNUM > 10 | 永遠返回 0 行 | ROWNUM 從 1 開始遞增,首行即為 1,不滿足 >10 被過濾,後續行永無機會獲得 ROWNUM=2 |
WHERE ROWNUM BETWEEN 11 AND 20 | 0 行 | 同理,首行 ROWNUM=1 不滿足 BETWEEN |
ORDER BY 後 WHERE ROWNUM <= 10 | 結果非預期排序 | ROWNUM 在 ORDER BY 前分配,先取前 10 行再排序 |
外連接替代 NOT IN / IN 高效模式
問題場景
-- 低效寫法:NOT IN 子查詢
SELECT t1.id, t1.value
FROM t1
WHERE t1.value NOT IN (SELECT value FROM t2);
-- 效能災難:對 t1 每行掃描 t2 全表,無法使用索引,大表時呈 N×M 指數級增長
外連接改寫 (標準 ANSI SQL)
-- 替代 NOT IN:LEFT JOIN + IS NULL
SELECT t1.id, t1.value
FROM t1
LEFT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NULL;
-- 替代 IN:INNER JOIN (或 LEFT JOIN + IS NOT NULL)
SELECT t1.id, t1.value
FROM t1
INNER JOIN t2 ON t1.value = t2.value;
-- 或
SELECT t1.id, t1.value
FROM t1
LEFT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NOT NULL;
為什麼外連接快?
- Hash Join / Sort Merge Join:優化器可選擇高效連接算法,一次掃描雙表構建雜湊表/排序流,複雜度 O(N+M)。
- 索引利用:連接鍵上有索引時,可用 Nested Loops + Index Lookup。
- 並行執行:連接操作易於並行化 (PX),子查詢
NOT IN 往往強制串行。
實測對比 (T1: 100萬行, T2: 50萬行, 值重複度高)
| 寫法 | 執行時間 | 執行計劃關鍵操作 |
| NOT IN 子查詢 | 18 分 32 秒 | FILTER + TABLE ACCESS FULL (T2) per row |
| NOT EXISTS 子查詢 | 2.1 秒 | HASH JOIN RIGHT ANTI |
| LEFT JOIN + IS NULL | 1.8 秒 | HASH JOIN RIGHT OUTER + FILTER |
| IN 子查詢 | 15 分 47 秒 | FILTER + TABLE ACCESS FULL (T2) per row |
| INNER JOIN | 1.5 秒 | HASH JOIN |
NOT EXISTS vs LEFT JOIN IS NULL
兩者邏輯等價且效能相近。NOT EXISTS 語義更清晰 (關聯子查詢),LEFT JOIN IS NULL 可直接選取右表欄位供調試。建議:
- 純存在性檢查 →
NOT EXISTS
- 需檢查右表其他欄位/調試 →
LEFT JOIN IS NULL
- **避免
NOT IN** 當子查詢可能返回 NULL 時 (單個 NULL 導致整體結果為空)
最佳實踐清單
- [ ] 分頁查詢**必須**有確定
ORDER BY,且唯一鍵作為最後排序鍵 (避免同值行順序不穩定導致翻頁重複/遺漏)。
- [ ] 12c+ 優先使用
OFFSET/FETCH,優化器支援更好。
- [ ] 所有
NOT IN 子查詢重構為 NOT EXISTS 或 LEFT JOIN IS NULL。
- [ ] 連接鍵建立索引,統計信息及時收集。
- [ ] 大表分頁考慮
ROWID 分頁或游標分頁 (Keyset Pagination) 替代 OFFSET。
copyright ©
萬盛學電腦網 all rights reserved