前言
SQL 語句寫法直接決定執行計畫好壞。本文針對 Oracle 環境，系統整理操作符優化、寫作規範、索引失效場景、Hint 使用原則，並對比常見誤區，助你寫出高效 SQL。
一、操作符優化：避坑指南
| 操作符 | 問題 | 推薦替代方案 |
|---|
| IN | 需轉換為多表連接，含群組統計時無法轉換，多一層解析開銷 | 業務核心 SQL 改用 EXISTS 或 JOIN |
| NOT IN | 無法使用索引，且含 NULL 時語義陷阱 | NOT EXISTS 或 LEFT JOIN + IS NULL |
| != / <> | 永不走索引，導致全表掃描 | 改寫為 a > 0 OR a < 0、a > '' 等區間查詢 |
| IS NULL / IS NOT NULL | B-Tree 索引不存 NULL，通常全掃 | 1) 設 NOT NULL + 預設值 2) 位圖索引（低基數、少 DML） 3) 改寫 a > 0 |
| LIKE '%abc%' | 前導萬用字元無法走索引範圍掃描 | 若知前綴改 LIKE 'X5400%'；或用 Oracle Text / 函數索引 |
| UNION | 隱含 SORT UNIQUE，大量資料落磁碟排序 | 確知無重複時用 UNION ALL |
二、SQL 寫作規範：共享游標與解析優化
- 統一大小寫、縮排、別名：Oracle 依字面比對 Shared Pool，完全相同才能共享游標，減少 Hard Parse。
- 加上 Schema 前綴：
SELECT * FROM scott.emp 避免解析時搜尋同義詞/公共同義詞。
- WHERE 條件順序：無索引全掃時，把過濾率高（選擇率低）的條件前置，減少 CPU 比較次數。
- FROM 表順序：無統計資訊時 Oracle 按書寫順序驅動，小表應寫在前；有統計則由 CBO 自動決定。
三、索引失效常見模式與改寫
| 失效寫法 | 原因 | 改寫範例 |
|---|
SUBSTR(col,1,4)='5400' | 函數作用於索引欄 | col LIKE '5400%' |
TRUNC(date_col)=TRUNC(SYSDATE) | 函數阻擋索引 | date_col >= TRUNC(SYSDATE) AND date_col < TRUNC(SYSDATE)+1 |
col+5 = 100 | 運算式導致隱式轉換 | col = 95 |
'X'||col > 'X5400' | 連接運算破壞索引順序 | col > '5400' |
col1 || col2 = 'AB' | 多欄運算無法使用單欄索引 | col1='A' AND col2='B' 或建立複合索引 |
四、Hint 使用原則
Hint 只是建議，非強制。優先從統計資訊、索引設計、SQL 改寫著手；Hint 作為最後手段，且需在多環境驗證無副作用。
| 分類 | 常用 Hint | 說明 |
|---|
| 目標模式 | /*+ ALL_ROWS */ /*+ FIRST_ROWS(n) */ | 全表最佳化 / 前 n 列快速回應 |
| 連接方法 | USE_NL USE_HASH USE_MERGE | 指定 Nested Loop / Hash Join / Sort Merge Join |
| 索引指定 | INDEX(t idx_name) INDEX_FFS(t idx_name) | 強制索引範圍掃描 / 快速全索引掃描 |
| 並行 | PARALLEL(t,8) PARALLEL_INDEX(t, idx, 4) | 並行查詢/索引掃描 |
五、ERP 上線前五大 SQL 品質檢核
- 核心交易 SQL 無
NOT IN、!=、LIKE '%...%'、函數索引欄。
- 所有表、索引已收集統計資訊（
DBMS_STATS.GATHER_SCHEMA_STATS）。
- 執行計畫無
FULL TABLE SCAN（小表除外）、無 CARTESIAN PRODUCT。
- 綁定變數一致性：同一邏輯 SQL 文字完全相同，確保游標共享。
- 已建立
SQL Plan Baseline 或 SQL Profile 固定關鍵 SQL 計畫。
