Oracle SQL 效能優化關鍵技巧：IN/NOT IN/<>/IS NULL/LIKE/UNION 改寫指南

替代方案：

-- 低效
SELECT * FROM Products WHERE ProductID NOT IN (SELECT ProductID FROM Discontinued);

-- 高效：NOT EXISTS
SELECT * FROM Products p WHERE NOT EXISTS (SELECT 1 FROM Discontinued d WHERE d.ProductID = p.ProductID);

-- 高效：LEFT JOIN + IS NULL
SELECT p.* FROM Products p LEFT JOIN Discontinued d ON p.ProductID = d.ProductID WHERE d.ProductID IS NULL;

3. <> (不等於) 操作符

特性：永遠不使用索引，導致全表掃描。

改寫 (需確認資料分布)：

WHERE status <> 0    -- 改為
WHERE status > 0 OR status < 0

WHERE code <> ''     -- 改為
WHERE code > ''      -- 若 code 為非負字串編碼

4. IS NULL / IS NOT NULL

特性：B-tree 索引不儲存 NULL，故 IS [NOT] NULL 通常全表掃描。

改寫：

WHERE col IS NOT NULL   -- 改為
WHERE col > 0           -- 數值型
WHERE col > ''          -- 字串型

-- 或：不允許 NULL，給預設值
ALTER TABLE T MODIFY col DEFAULT 0 NOT NULL;

替代索引**：位圖索引 可索引 NULL，但不適用高併發 OLTP (鎖競爭劇烈)，僅適合資料倉儲/低更新表。

5. > / < 操作符優化

同一欄位、同一常數，>= 比 > 可能更優：

-- 假設 A 欄位值分布：30萬=0, 30萬=1, 39萬=2, 1萬=3
WHERE A > 2    -- 優化器先找 =2 的索引項再比較
WHERE A >= 3   -- 直接定位 =3 的索引項，更快

6. LIKE 操作符

前置萬用字元無法用索引：LIKE '%5400%' → 全表掃描。

改寫：若知道前綴模式，拆解為多個前綴比對：

-- 低效
WHERE YY_BH LIKE '%5400%'

-- 高效：已知前綴為 'X' 或 'B'
WHERE YY_BH LIKE 'X5400%' OR YY_BH LIKE 'B5400%'  -- 兩次索引範圍掃描

7. UNION vs UNION ALL

UNION**：合併後排序去重，大量資料可能落磁碟排序。

UNION ALL**：直接合併，**無排序去重**，效能極高。

建議：業務確定無重複或允許重複時，**永遠用 UNION ALL**。

SELECT * FROM CurrentMonthSales
UNION ALL
SELECT * FROM HistorySales;

8. SQL 寫風格影響計畫

同樣功能，不同寫法可能產生不同計畫。建議：

• 統一團隊 SQL 風格指南 (大小寫、縮排、別名命名)。
• 使用格式化工具 (SQL Prompt, Poor Man's T-SQL Formatter)。
• 複雜查詢拆解為 CTE，逐步驗證中間結果。

總結對照表

將上述模式內化為編碼肌肉記憶，配合定期 EXPLAIN PLAN / SQL Monitoring 驗證，即能持續產出高效能 Oracle SQL。