萬盛學電腦網

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

Oracle 分頁查詢與外連接替代 NOT IN 實戰技巧

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

關鍵點解析

  1. 內層排序:ORDER BY 必須在最內層,確定行序。
  2. 中層 ROWNUM 截斷:WHERE ROWNUM <= endRow 限制輸出行數,優化器可推導 COUNT STOPKEY 操作,提前終止掃描。
  3. 外層過濾起始行:WHERE rn > startRow 過濾前 N 行。
  4. 別名衝突:外層別名 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 經典陷阱

外連接替代 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萬行, 值重複度高)

錯誤寫法現象原因
WHERE ROWNUM > 10永遠返回 0 行ROWNUM 從 1 開始遞增,首行即為 1,不滿足 >10 被過濾,後續行永無機會獲得 ROWNUM=2
WHERE ROWNUM BETWEEN 11 AND 200 行同理,首行 ROWNUM=1 不滿足 BETWEEN
ORDER BY 後 WHERE ROWNUM <= 10結果非預期排序ROWNUM 在 ORDER BY 前分配,先取前 10 行再排序

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
寫法執行時間執行計劃關鍵操作
NOT IN 子查詢18 分 32 秒FILTER + TABLE ACCESS FULL (T2) per row
NOT EXISTS 子查詢2.1 秒HASH JOIN RIGHT ANTI
LEFT JOIN + IS NULL1.8 秒HASH JOIN RIGHT OUTER + FILTER
IN 子查詢15 分 47 秒FILTER + TABLE ACCESS FULL (T2) per row
INNER JOIN1.5 秒HASH JOIN