Oracle SQL 優化核心規則：IN/EXISTS、NOT IN/NOT EXISTS、<>、IS NULL、通配符、UNION/ALL 實戰指南

核心優化口訣

「慎用 IN、禁用 NOT IN、避開 <>、拒絕 IS NULL、謹慎通配符、優先 UNION ALL」

六大高頻優化規則詳細解析

1. IN vs EXISTS：子查詢轉換開銷

-- 低效：IN 子查詢
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE vip = 'Y');

-- 高效：EXISTS 相關子查詢
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.vip = 'Y');

• IN：Oracle 嘗試轉換為 Semi-Join，失敗則先執行子查詢產生臨時結果集再 Join。至少多一次轉換/物化開銷。
• EXISTS：對外層每行執行子查詢，命中即停止 (短路)，適合外層小、內層有索引。
• 選擇原則：外層結果集小 → EXISTS；子查詢結果集小且無相關欄位 → IN (可轉換 Hash Semi-Join)；不確定時優先 EXISTS。

2. NOT IN vs NOT EXISTS / Outer Join + IS NULL

-- 危險：NOT IN (含 NULL 時邏輯錯誤 + 全表掃描)
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE status = 'INACTIVE');

-- 安全高效：NOT EXISTS
SELECT * FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.status = 'INACTIVE');

-- 替代：Left Outer Join + IS NULL
SELECT o.* FROM orders o LEFT JOIN customers c ON o.customer_id = c.id AND c.status = 'INACTIVE' WHERE c.id IS NULL;

• NOT IN：子查詢結果含 NULL 時整體判斷為 UNKNOWN，導致邏輯錯誤；且無法使用索引，必然全表掃描。
• NOT EXISTS：正確處理 NULL，可利用索引 (Nested Loop Anti-Join)。
• 強烈建議：全面禁用 NOT IN，改用 NOT EXISTS 或 Outer Join + IS NULL。

3. <> / != 避免全表掃描

-- 低效：不等於永遠不走索引
SELECT * FROM accounts WHERE balance <> 0;

-- 高效：改寫為範圍查詢 (假設 balance >= 0)
SELECT * FROM accounts WHERE balance > 0;

-- 字串不等於
SELECT * FROM users WHERE status <> 'INACTIVE';
-- 改寫
SELECT * FROM users WHERE status > 'INACTIVE' OR status < 'INACTIVE';

• B-Tree 索引不存儲「不等於」資訊，<> 必觸發 Full Table Scan。
• 改寫為 > / < / BETWEEN / IN 等範圍條件，即可走索引範圍掃描。

4. IS NULL / IS NOT NULL 索引失效

• B-Tree 索引 不存儲完全為 NULL 的鍵值 (除非為複合索引且前導欄非 NULL)。
• WHERE col IS NULL 無法走單欄索引。

-- 解決方案
-- 1. 設計階段：NOT NULL 約束 + 預設值
ALTER TABLE orders MODIFY status DEFAULT 'NEW' NOT NULL;

-- 2. 函數索引 (11g+)
CREATE INDEX idx_orders_status_nvl ON orders (NVL(status, 'NULL_PLACEHOLDER'));
-- 查詢: WHERE NVL(status, 'NULL_PLACEHOLDER') = 'NULL_PLACEHOLDER'

-- 3. 位圖索引 (低基數欄位、DW 環境)
CREATE BITMAP INDEX idx_orders_status_bmp ON orders (status);
-- 位圖索引包含 NULL 值

5. 通配符 LIKE '%abc%' 導致全表掃描

-- 低效：前導通配符
SELECT * FROM customers WHERE phone LIKE '%5400%';

-- 高效：業務邏輯重構
-- 若 phone 格式固定 'X-5400-XXXX' 或 'B-5400-XXXX'
SELECT * FROM customers WHERE phone LIKE 'X-5400-%' OR phone LIKE 'B-5400-%';

• 前導 % 使索引範圍掃描失效。
• 後置 % (如 'ABC%') 可走索引範圍掃描。
• 若必須模糊搜尋，考慮 Oracle Text / JSON 搜尋 / 應用層搜尋引擎 (Elasticsearch)。

6. UNION vs UNION ALL：排序去重開銷

-- 低效：UNION 會 Sort + Unique
SELECT * FROM current_orders UNION SELECT * FROM history_orders;

-- 高效：UNION ALL 直接合併 (業務確認無重複)
SELECT * FROM current_orders UNION ALL SELECT * FROM history_orders;

• UNION：合併後 Sort → Unique → Return，大數據量可能溢寫 Temp 表空間。
• UNION ALL：零開銷直接串流回傳。
• 原則：業務邏輯確定無重複 (如分區表、時段表) → 強制 UNION ALL。

綜合優化檢查清單

1. 執行計畫確認：EXPLAIN PLAN / DBMS_XPLAN.DISPLAY_CURSOR。
2. 統計資訊最新：DBMS_STATS.GATHER_TABLE_STATS。
3. 索引存在度：USER_INDEXES / USER_IND_COLUMNS。
4. Bind Variable Peeking / Adaptive Cursor Sharing 影響。