Oracle SQL 優化關鍵技術：運算子、空值、LIKE 與改寫策略

核心原則

優化器對不同運算子的索引利用能力差異極大，**理解運算子語意與執行計畫影響，是 SQL 改寫的基石**。

一、IN 運算子

• 優點：可讀性好、易維護。
• 缺點：優化器嘗試轉換為多表連接；轉換失敗（含聚合、複雜子查詢）則先執行子查詢再外層過濾，**至少多一次轉換/執行開銷**。
• 建議：核心高頻 SQL **避免 IN**，改寫為 EXISTS 或直接連接。

二、NOT IN 運算子

• 嚴禁使用：無法使用索引；子查詢含 NULL 導致語意陷阱（結果集為空）。
• 替代：NOT EXISTS 或 OUTER JOIN ... WHERE IS NULL。

三、不等於運算子

• <>、!=、^= **永不使用索引** → 全表掃描。
• 改寫策略：
  • a <> 0 → a > 0 OR a < 0（可用索引範圍掃描）。
  • a <> '' → a > ''（字串）。

四、IS NULL / IS NOT NULL

• B-Tree 索引**不儲存全 NULL 鍵值** → 一般不走索引。
• 改寫：
  • a IS NOT NULL → a > 0（數值）、a > ''（字串）。
  • 業務允許時，欄位設 NOT NULL + 預設值（如狀態預設 'NEW'）。
  • Bitmap Index 可索引 NULL，**但分區表不可用、高基數欄位效能差、DML 鎖爭用劇增**，謹慎評估。

五、大於/小於運算子

• 通常可走索引範圍掃描。
• 邊界優化範例：表 100 萬行，A 欄位值分布 0:30萬、1:30萬、2:39萬、3:1萬。
  A > 2：優化器先定位 2 的索引區間再過濾。
  A >= 3：直接定位 3 的索引區間，**效率顯著更高**。
• 建議：優先使用 >= / <= 明確邊界，避免 > / < 配合常數邊界值。

六、LIKE 運算子

• LIKE '%abc%'：前導萬用字元 → **索引失效**，全表掃描。
• LIKE 'abc%'：後置萬用字元 → **索引範圍掃描**有效。
• 實戰改寫：營業編號 YY_BH LIKE '%5400%' 全掃描 → 已知前綴為 'X' 或 'B' → 改為 YY_BH LIKE 'X5400%' OR YY_BH LIKE 'B5400%'，**雙範圍掃描替代全掃描**，效能提升數量級。
• 其他方案：Oracle Text 全文檢索、Function-based Index (REVERSE(col))、反向鍵索引。

七、綜合改寫清單

總結

SQL 優化始於**運算子選擇**：拒絕 NOT IN、<>、前導 LIKE；善用 EXISTS、範圍邊界、Function-based Index。結合執行計畫驗證，將「寫得對」升級為「寫得快」。