Oracle IN 與 NOT IN 性能差異深度解析：為何 MINUS 改寫能大幅提升查詢速度

從 SQL Server 移植到 Oracle 的性能陷阱

系統從 SQL Server 移植至 Oracle 後，某「查找無子記錄的父記錄」查詢從 1 秒飆升至 10 秒以上。本文透過實測對比，揭示 Oracle 中 NOT IN 子查詢的性能陷阱，並提供 MINUS 集合運算優化方案。

業務場景與原始 SQL

父表 PARENT、子表 CHILDREN，需查詢「沒有孩子的父親」。子表資料量 50 萬筆。

-- 標準寫法：NOT IN 子查詢 (耗時 10 秒+)
SELECT * FROM PARENT 
WHERE ID NOT IN (SELECT PID FROM CHILDREN);

優化方案：MINUS 集合運算

-- 改寫後：MINUS 差集運算 (耗時 < 1 秒)
SELECT * FROM PARENT 
WHERE ID IN (
    SELECT ID FROM PARENT
    MINUS
    SELECT PID FROM CHILDREN
);

性能對比測試結果

深度原理分析

為何 NOT IN 效能差？

1. 空值陷阱：若子查詢結果含 NULL，NOT IN 整體結果為空集，Oracle 必須逐行檢查空值，無法使用索引。
2. 執行計畫劣化：大量資料時易退化為 FILTER 操作，對外部表每筆資料掃描內部表，呈現 Nested Loop 特性，複雜度 O(M×N)。
3. 反連接轉換失敗：Oracle 優化器嘗試轉為 HASH JOIN ANTI，但受限於統計資訊、空值、相關子查詢等條件常轉換失敗。

為何 MINUS 高效？

1. 集合運算優化：MINUS 觸發 HASH SET OPERATION，雙方均可全表掃描並建立雜湊表，複雜度 O(M+N)。
2. 避開空值語意：集合運算自動處理重複值與空值，語意更清晰。
3. 穩定執行計畫：不依賴反連接轉換，效能表現穩定可預期。

其他優化替代方案

-- 1. NOT EXISTS (推薦，語意清晰，易產生 HASH JOIN ANTI)
SELECT * FROM PARENT P
WHERE NOT EXISTS (SELECT 1 FROM CHILDREN C WHERE C.PID = P.ID);

-- 2. LEFT JOIN + IS NULL (傳統寫法)
SELECT P.* FROM PARENT P
LEFT JOIN CHILDREN C ON P.ID = C.PID
WHERE C.PID IS NULL;

-- 3. 外部連接 + 過濾 (舊版 Oracle 常用)
SELECT P.* FROM PARENT P, CHILDREN C
WHERE P.ID = C.PID(+) AND C.PID IS NULL;

最佳實踐建議

• 優先使用 NOT EXISTS：語意明確、效能穩定、支援相關子查詢、無空值陷阱。
• 避免 NOT IN 子查詢：除非確定子查詢結果無 NULL 且資料量極小。
• 大量資料差集運算：MINUS/INTERSECT 是強大工具，善用集合思維替代逐行比對。
• 收集統計資訊：DBMS_STATS.GATHER_TABLE_STATS 讓優化器選擇最佳計畫。

結語

Oracle 與 SQL Server 優化器邏輯差異巨大，移植專案必須重新審視 SQL 寫法。本案例證明：集合運算 (MINUS) 在大數據量差集查詢中遠優於 NOT IN，開發者應建立集合思維，善用 Oracle 強大的集合運算優化能力。