Oracle 索引使用規則與優化器決策 12 大關鍵因素

前言

「為什麼我建了索引卻沒被用到？」這是經典難題。Oracle 優化器 (CBO/RBO) 決定是否使用索引，取決於統計資訊、SQL 寫法、連接方式等多重因素。本文整理 12 大關鍵判斷準則，助你診斷執行計畫。

1. 優化器模式

• OPTIMIZER_MODE = CHOOSE (預設)：有統計資訊用 CBO，無則用 RBO。
• ALL_ROWS：傾向全表掃描、Hash Join，適合批次/報表。
• FIRST_ROWS(n)：傾向索引巢狀迴圈、快速回應前 n 列，適合互動式查詢。
• 可用 Hint /*+ FIRST_ROWS(10) */ 覆蓋 Session 級設定。

2. WHERE 條件是否包含索引前導欄位

**必要條件**：索引定義的**第一欄位**必須出現在 WHERE / JOIN / ON 條件中，否則無法進行 INDEX RANGE SCAN (除非 INDEX FULL SCAN / FAST FULL SCAN)。

3. 連接方式影響索引使用

→ 可用 /*+ USE_NL(b) */ 強制 NL 以利用索引。

4. 連接順序決定外表/內表

NL 中**外表 (驅動表) 通常全表掃描**，內表才用索引。若大表當外表、小表有索引當內表，效率極差。
可用 /*+ ORDERED */ 指定 FROM 子句順序為連接順序，配合 LEADING Hint 控制驅動表。

5. 系統字典表/視圖未分析

查詢 DBA_*, V$* 等視圖時，因無統計資訊易產生劣質計畫。**切勿自行 ANALYZE 字典表**，會導致死鎖或效能雪崩。

6. 索引欄位被函數/運算式包裹

WHERE UPPER(name) = 'JOHN' → 普通索引失效，需建立 UPPER(name) 函數式索引。
WHERE salary + bonus > 10000 → 同理失效。

7. 隱性資料型態轉換

WHERE char_col = 123 → Oracle 隱性 TO_NUMBER(char_col)，導致索引失效。
**嚴禁** 字串與數值直接比較，應統一型態或顯式轉換。

8. 統計資訊是否最新

頻繁 DML 的表需定期 DBMS_STATS.GATHER_TABLE_STATS(..., CASCADE=>TRUE) (含索引統計)。
缺乏統計 → 優化器假設均勻分布 → 錯誤基數估算 → 錯誤計畫。

9. 索引選擇性不足

欄位 distinct values 過少 (如狀態碼 0/1/2)，優化器判定掃描成本 > 全表掃描 → 忽略索引。
解法：建立**直方圖** DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR COLUMNS SIZE AUTO status') 讓優化器識別熱門值。

10. 索引欄位允許 NULL

B-tree 索引**不儲存全 NULL 鍵值**。
WHERE col IS NULL、COUNT(*) (需掃描全表) 無法使用該索引。
解法：建立 (col, 1) 複合索引或將欄位設為 NOT NULL。

11. 並行查詢 (PQO) 會忽略索引

/*+ PARALLEL(t, 4) */ 或 PARALLEL_DEGREE_POLICY=AUTO 觸發並行 → 傾向全表掃描 + 並行分發，索引通常不被考慮。

12. 綁定變數窺探與適應性游標共享

綁定變數 :b1 在首次硬解析時「窺探」值決定計畫，後續不同值複用同計畫可能導致效能劇烈波動 (Bind Variable Peeking)。
11g+ _OPTIM_PEEK_USER_BINDS=FALSE 或使用 /*+ BIND_AWARE */、/*+ OPT_PARAM('_optim_peek_user_binds','false') */ 緩解。

診斷清單

1. 執行 EXPLAIN PLAN 或 DBMS_XPLAN.DISPLAY_CURSOR 確認實際操作 (INDEX RANGE SCAN vs FULL TABLE SCAN)。
2. 檢查 USER_TAB_COL_STATISTICS、USER_INDEXES 統計時間。
3. 確認 WHERE 條件型態、函數、NULL、OR 等索引殺手。
4. 嘗試 Hint 強制索引，若效能變好 → 統計/參數問題；若變差 → 索引確實不適合。