Oracle SQL 語句優化核心技巧：運算子與改寫實戰

運算子效能陷阱與改寫策略

IN 運算子

優點：語法清晰、易維護。缺點：Oracle 需嘗試轉換為多表連接，轉換失敗時先執行子查詢再掃描外層表，至少增加一次轉換開銷。含 GROUP BY 等複雜子查詢無法轉換。

建議：核心高頻 SQL 盡量避免 IN，改用 EXISTS 或連接寫法。

NOT IN 運算子

強烈不建議使用：無法使用索引，且若子查詢結果含 NULL 會導致邏輯錯誤（永遠回傳空結果）。

替代方案：NOT EXISTS、LEFT JOIN ... WHERE IS NULL。

<> (不等於) 運算子

永遠不會使用索引，導致全表掃描。

改寫範例：

• a <> 0 → a > 0 OR a < 0
• a <> '' → a > ''（字串型）

IS NULL / IS NOT NULL

B-tree 索引不存儲 NULL 值，故無法走索引。

改寫思路：

• 業務允許時，設定 NOT NULL 約束 + 預設值（如狀態欄位預設 '申請'）。
• 改寫為 a > 0 或 a > ''（視資料型別）。
• 極少數情況可考慮位圖索引，但需注意：分區表不支援、高基數欄位效能差、並發 DML 易產生鎖爭用。

> / < 運算子的邊界優化

一般情況可走索引，但邊界值分布不均時有差異。例：A 欄位 30 萬筆=0、30 萬筆=1、39 萬筆=2、1 萬筆=3。
A > 2 需先定位到 2 的索引再比較；A >= 3 直接定位 3 的索引，效率更高。

LIKE 運算子與通配符策略

LIKE '%5400%' 導致全表掃描；LIKE 'X5400%' 可走範圍掃描。
實戰案例：營業編號 YY_BH LIKE '%5400%' → 改為 YY_BH LIKE 'X5400%' OR YY_BH LIKE 'B5400%'，利用索引進行兩次範圍掃描，效能大幅提升。

優化原則總結

1. 優先讓 WHERE 條件列走索引（避免函數、運算、隱性轉換）。
2. 用 EXPLAIN PLAN 驗證執行計畫，關注「全表掃描」「笛卡兒積」「高成本操作」。
3. 統計資訊及時收集（DBMS_STATS），是優化器生成最佳計畫的前提。
4. 針對核心 SQL 建立 SQL Profile / SQL Plan Baseline 固定優質計畫。
5. 改寫前後必須在測試環境對比邏輯結果與效能指標（邏輯讀、執行時間、CPU）。

SQL 優化無銀彈，唯有「理解優化器行為 + 掌握改寫模式 + 持續驗證」方能行穩致遠。