Oracle 分頁查詢與 NOT IN 改寫外連接：兩大 SQL 進階技巧實戰

技巧一：取得第 6-10 筆資料 (分頁核心邏輯)

假設表 T(ID PK, VALUE)。

方法 1：MINUS 集合運算 (直觀但效能較差)

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

執行兩次全表/索引掃描，再排序差集。

方法 2：內聯視圖 + ROWNUM 過濾 (標準高效寫法)

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

• 內層 WHERE ROWNUM <= 10 讓 Oracle **提前停止** 掃描 (COUNT STOPKEY)。
• 外層過濾 RN > 5 取得第 6-10 筆。
• **通用分頁模板**：WHERE ROWNUM <= :end_row 內層，WHERE RN > :start_row 外層。

技巧二：外連接替代 NOT IN / IN (效能提升數量級)

場景：主表 T1(ID PK, VALUE)，對照表 T2(VALUE)。

原寫法 (低效)

-- IN
SELECT * FROM T1 WHERE VALUE IN (SELECT VALUE FROM T2);
-- NOT IN
SELECT * FROM T1 WHERE VALUE NOT IN (SELECT VALUE FROM T2);

執行計劃多為 FILTER + 子查詢逐行比對，資料量萬級以上極慢。

改寫：LEFT OUTER JOIN (+ IS NULL / IS NOT NULL)

-- 替代 IN
SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE(+)   -- (+) 標示右表外連
  AND T2.VALUE IS NOT NULL;    -- 過濾掉無匹配行

-- 替代 NOT IN
SELECT T1.ID, T1.VALUE
FROM T1, T2
WHERE T1.VALUE = T2.VALUE(+)
  AND T2.VALUE IS NULL;        -- 保留無匹配行

• Oracle 將外連接轉為 **HASH JOIN / MERGE JOIN**，一次掃描完成，複雜度 O(N+M) 而非 O(N×M)。
• (+) 僅支援單表外連，複雜條件建議改用 ANSI SQL: LEFT JOIN T2 ON T1.VALUE = T2.VALUE。

實測效能對比 (萬筆資料)

注意事項

1. NOT IN 子查詢若含 NULL 值會導致**全表無結果** (三值邏輯陷阱)，外連接無此問題。
2. IN 子查詢在 10g+ 常自動轉為 SEMI JOIN (高效)，但 NOT IN 無法自動轉，手工改寫收益最大。
3. ANSI 外連接語法可移植性更好，新專案建議優先使用。

總結

分頁用 ROWNUM 內聯視圖 + STOPKEY；NOT IN/IN 大量資料**必改外連接**，效能提升百倍且規避 NULL 陷阱。