Oracle SQL 調優核心原則：從索引失效陷阱到執行計畫優化實戰

前言：為什麼 SQL 調優決定系統生死

開發期資料量小，SQL 寫法差異不明顯；上線後資料暴增，劣質 SQL 與優質 SQL 速度可差**百倍以上**。Oracle 以成本基礎優化器（CBO）為核心，**索引使用與否是性能分水嶺**。掌握優化器「拒絕索引」的規則，是寫出高效 SQL 的前提。

WHERE 子句常見導致全表掃描的寫法陷阱

1. IS NULL / IS NOT NULL

B-tree 索引**不儲存全 NULL 的索引鍵**。任何包含 `IS NULL` 或 `IS NOT NULL` 的條件，優化器**必走全表掃描**。
對策：業務允許時給預設值、建立函數式索引（`CREATE INDEX idx ON t(NVL(col, 'X'))`）、或改用位圖索引（低基數欄位）。

2. 串聯欄位（`||`）

`WHERE first_name || ' ' || last_name = 'Bill Clinton'` → 索引失效。
對策：拆分條件 `WHERE first_name = 'Bill' AND last_name = 'Clinton'`；若為變數，應用層拆分後帶入。

3. LIKE 通配符前置（`%xxx%` / `%xxx`）

`LIKE '%clinton%'` → 全表掃描。
`LIKE 'clin%'` → 可用索引範圍掃描。
對策：全文檢索、Oracle Text、或反轉欄位建索引（`REVERSE(col)`）。

4. 索引欄位上做運算/函數

`WHERE SAL * 12 > 25000`、`WHERE TO_CHAR(date_col, 'YYYY') = '2023'` → 索引失效。
對策：改寫為 `WHERE SAL > 25000/12`、`WHERE date_col BETWEEN DATE '2023-01-01' AND DATE '2023-12-31'`；必要時建函數式索引。

5. NOT / <> / !=

`WHERE status <> 'INVALID'` → 通常全表掃描。
對策：改寫為 `WHERE status < 'INVALID' OR status > 'INVALID'`（若有索引且選擇率高）；或 `WHERE status IN ('VALID','PENDING')` 明確列舉正向值。

6. IN vs EXISTS

• `IN` 子查詢：先執行子查詢產生結果集 → 存臨時表 → 主查詢 JOIN。
• `EXISTS` 相關子查詢：對主查詢每列 → 執行子查詢 → 找到第一筆即停止（半連接）。

**結論**：`EXISTS` 通常更快，尤其子查詢結果集大、主查詢過濾後行數少時。`NOT EXISTS` 亦優於 `NOT IN`（後者需處理 NULL 三值邏輯）。

ORDER BY 與排序優化

• `ORDER BY` 非索引欄位或含運算式 → 需額外排序（Sort 操作）。
• 解法：建立符合排序順序的複合索引；避免 `ORDER BY UPPER(name)` 等運算。
• 大量排序溢出記憶體（`SORT_AREA_SIZE`/`PGA_AGGREGATE_TARGET`）會觸發**磁碟排序**，效能暴跌 ~14,000 倍。調大排序區域、或用索引消除排序。

三大 SQL 調優核心目標

1. 消除不必要的大表全表掃描：回傳行數 < 40%（有序表）或 < 7%（無序表）時應走索引。手段：加索引、Hint 強制索引、重寫 SQL。
2. 小表全表掃描放入 KEEP Pool：`ALTER TABLE small CACHE`（Oracle 7）或 `ALTER TABLE small STORAGE (BUFFER_POOL KEEP)`（8i+），避免重複物理讀。
3. 確保最優索引與 JOIN 方法：Nested Loops（小結果集驅動大表索引查找）、Hash Join（大表關聯、無索引）、Sort-Merge Join（已排序輸入）。分析執行計畫，必要時 Hint 指定。

實戰調優工具鏈

• `EXPLAIN PLAN` / `DBMS_XPLAN.DISPLAY_CURSOR`：查看執行計畫、Cardinality、Cost、Access Path。
• `SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS`：即時統計一致性獲取、邏輯讀、物理讀、排序次數。
• SQL Trace / TKPROF / Enterprise Manager / SQL Developer：深度分析。
• 統計資訊：`DBMS_STATS.GATHER_TABLE_STATS` 定期收集，**統計過期是 CBO 產生錯誤計畫的首因**。

總結

Oracle SQL 調優無銀彈，但**避開索引失效陷阱、善用 EXISTS/NOT EXISTS、控制排序、維護統計資訊、讀懂執行計畫**五大基本功，能解決 90% 以上效能問題。複雜案例再引入 Hint、Profile、Baseline、SQL Plan Management 等進階手段。