Oracle 分頁查詢與外連結替代 NOT IN 的高效寫法實戰

Oracle 高效分頁與反模式優化實戰

本文通過具體案例演示如何高效獲取指定範圍記錄（第 6-10 條），並深度解析為何外連結（`+` 符號）能大幅優於 `IN`/`NOT IN` 子查詢，提供可直接落地的 SQL 改寫範本。

一、獲取第 6-10 條記錄的三種方案對比

假設測試表結構：

CREATE TABLE T (ID VARCHAR2(4) PRIMARY KEY, VALUE INT);

方案 1：MINUS 集合運算（不推薦，性能差）

SELECT * FROM T WHERE ROWNUM <= 10
MINUS
SELECT * FROM T WHERE ROWNUM <= 5;

缺點：需掃描兩次表，集合運算開銷大，無法利用索引排序。

方案 2：子查詢 + ROWNUM 別名（推薦，兼容 10g 及以上）

SELECT ID, VALUE FROM (
    SELECT ID, VALUE, ROWNUM RN FROM T WHERE ROWNUM <= 10
) WHERE RN > 5;

優勢：單次掃描，`ROWNUM <= 10` 可觸發 `COUNT STOPKEY` 優化，執行計劃顯示 `STOPKEY` 操作。

方案 3：分析函數 `ROW_NUMBER()`（12c+ 推薦，語義最清晰）

SELECT ID, VALUE FROM (
    SELECT ID, VALUE, ROW_NUMBER() OVER (ORDER BY ID) RN FROM T
) WHERE RN BETWEEN 6 AND 10;

優勢：支援複雜排序，12c 可直接用 `OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY`。

二、外連結替代 NOT IN 的原理與實戰

為何 IN / NOT IN 效率低？

當子查詢結果集超過萬行時，Oracle 傾向採用 `FILTER` 操作逐行比對，複雜度接近 O(M×N)，極易導致全表掃描與緩衝區熱點。

外連結改寫模板

假設主表 `T1`，關聯表 `T2`：

CREATE TABLE T1 (ID VARCHAR2(4) PRIMARY KEY, VALUE INT);
CREATE TABLE T2 (VALUE INT);

替代 `IN` 子查詢

-- 原寫法（低效）
SELECT * FROM T1 WHERE VALUE IN (SELECT VALUE FROM T2);

-- 外連結改寫（高效）
SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE(+)
  AND T2.VALUE IS NOT NULL;

替代 `NOT IN` 子查詢

-- 原寫法（極低效，且 NULL 值導致語義陷阱）
SELECT * FROM T1 WHERE VALUE NOT IN (SELECT VALUE FROM T2);

-- 外連結改寫（高效，正確處理 NULL）
SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE(+)
  AND T2.VALUE IS NULL;

核心機制解析

• `T2.VALUE(+)` 表示右外連結，T2 無匹配行時返回 `NULL`
• `IS NOT NULL` 過濾出有匹配行（等價 `IN`）
• `IS NULL` 過濾出無匹配行（等價 `NOT IN`，且不受 NULL 干擾）
• 優化器可將其轉換為 `HASH JOIN` 或 `MERGE JOIN`，複雜度降為 O(M+N)

三、現代寫法建議 (12c+)

雖然 `(+)` 語法仍支援，但建議使用 ANSI SQL-99 標準語法，可讀性更強：

-- 替代 IN
SELECT T1.ID, T1.VALUE FROM T1
INNER JOIN T2 ON T1.VALUE = T2.VALUE;

-- 替代 NOT IN
SELECT T1.ID, T1.VALUE FROM T1
LEFT JOIN T2 ON T1.VALUE = T2.VALUE
WHERE T2.VALUE IS NULL;

四、性能驗證建議

1. 使用 `AUTOTRACE` 或 `DBMS_XPLAN.DISPLAY_CURSOR` 對比執行計劃
2. 關注 `FILTER` vs `HASH JOIN`、`BUFFER SORT` 操作消失
3. 測試數據量：主表 100萬，子表 10萬，觀察邏輯讀減少 90%+