Oracle 優化器為何不使用索引？12 大核心原因與排查實戰

前言

「明明建了索引，執行計劃卻走 Full Table Scan」是 DBA 與開發最常見的困惑。Oracle 優化器 (CBO/RBO) 依據統計資訊、SQL 寫法、參數設定綜合判斷存取路徑。本文系統梳理 12 大導致索引失效的核心原因，提供排查順序、驗證 SQL 與 Hint 強制技巧，助你快速定位根因。

一、SQL 執行計劃生成 9 步驟

1. 語法分析
2. 語義分析（物件存在、權限）
3. 視圖轉換
4. 表達式轉換
5. 選擇優化器 (RBO/CBO)
6. 選擇連接方式 (NL/SMJ/HJ)
7. 選擇連接順序
8. 選擇資料存取路徑 (Index/Full Scan/Partition 等)
9. 執行計劃

二、優化器模式確認

SHOW PARAMETER optimizer_mode;
-- 預設 CHOOSE：有統計用 CBO，無統計用 RBO
-- 建議 10g+ 固定 ALL_ROWS 或 FIRST_ROWS_n

三、12 大索引失效核心原因與驗證

1. WHERE 條件未包含索引前導欄位

-- 索引 idx(a,b,c)
WHERE b = 10;           -- ❌ 未用到 a，索引失效
WHERE a = 1 AND c = 2;  -- ✅ 用到前導欄 a，可用 Index Range Scan

驗證：檢查執行計劃 INDEX RANGE SCAN vs TABLE ACCESS FULL。

2. 連接方式限制索引使用

• Nested Loops (NL)：內表 (Inner) 的連接欄有索引 → 可用。
• Sort Merge Join (SMJ)：索引僅能避免排序，無法用於存取。
• Hash Join (HJ)：建立 Hash 表，索引完全不參與。

對策：/*+ USE_NL(t2) */ 強制 NL；或收集統計讓 CBO 選擇 NL。

3. 連接順序導致外表無法用索引

-- emp.deptno 有索引，dept.deptno 無索引
WHERE emp.deptno = dept.deptno
-- 若 CBO 選 dept 為 Outer (Driving) 表
-- emp 成為 Inner 表，但 NL 只能用 Inner 表的索引
-- Outer 表 emp 只能 Full Scan

對策：/*+ ORDERED */ 或 /*+ LEADING(emp) */ 指定驅動表。

4. 系統字典表/視圖未分析導致劣質計劃

查詢涉及 DBA_*、V$* 等字典視圖時，CBO 缺乏統計資訊。嚴禁手動分析 SYS 物件，改用 /*+ RULE */ 或 Materialize 子查詢隔離。

5. 索引欄位參與函數運算

WHERE UPPER(name) = 'SMITH'      -- ❌ 失效
WHERE TRUNC(dt) = SYSDATE        -- ❌ 失效
WHERE name = 'SMITH'             -- ✅ 生效

對策：建立 函數索引 CREATE INDEX idx_upper_name ON t(UPPER(name))；或應用層傳入已轉換值。

6. 隱含資料型別轉換

WHERE char_col = 123  -- Oracle 隱含 TO_NUMBER(char_col) → 等同函數運算 → 索引失效

對策：應用層嚴格型別綁定；或建立虛擬欄位/函數索引匹配轉換邏輯。

7. 統計資訊過期/缺失

-- 定期收集
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', CASCADE => TRUE);
-- 大表採樣
EXEC DBMS_STATS.GATHER_TABLE_STATS(..., ESTIMATE_PERCENT => 10);

無統計 → CBO 退化為預設選擇率 (1/100) → 計劃偏差極大。

8. 索引選擇性不足

-- 100萬行，deptno 僅 4 個值 (10/20/30/40)，各 25 萬
WHERE deptno = 10  -- 選擇率 25%，CBO 認為 Full Scan 更划算

對策：建立 Histogram (直方圖) 讓 CBO 識別傾斜分布：

EXEC DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT => 'FOR COLUMNS SIZE 254 DEPTNO');

9. 索引欄位允許 NULL

• B-tree 索引 不儲存全 NULL 鍵值。
• WHERE col IS NULL、COUNT(*) 無法使用該索引。

對策：WHERE col IS NULL 改用 NVL(col, 'NULL_FLAG') + 函數索引；或將欄位設 NOT NULL。

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

/*+ PARALLEL(t, 4) */ 或 PARALLEL_DEGREE_POLICY = AUTO 時，CBO 傾向 Full Scan + PX，忽略索引。

11. Bind Variable Peeking 導致計劃固化

WHERE col > :b1  -- 首次執行 :b1=1000 (選擇率高) → Full Scan 計劃被 Cursor Cache
-- 後續 :b1=1 (選擇率極低) 卻復用 Full Scan 計劃

對策：11g+ _OPTIM_PEEK_USER_BINDS=FALSE、ADAPTIVE_CURSOR_SHARING、/*+ BIND_AWARE */、或使用 Literal SQL (視情況)。

12. 索引損壞/不可用/隱藏

SELECT index_name, status, visibility
FROM user_indexes
WHERE table_name = 'EMP';

STATUS = UNUSABLE 或 VISIBILITY = INVISIBLE 時不可用。

四、系統化排查流程

1. 確認 OPTIMIZER_MODE 與統計資訊新鮮度。
2. 檢查 WHERE 條件是否覆蓋索引前導欄位。
3. 查看執行計劃連接方式 (NL/SMJ/HJ) 與順序。
4. 排除函數運算、隱含轉換、NULL 語義。
5. 檢視索引選擇性、Histogram 是否存在。
6. 確認無並行 Hint/參數干擾、Bind Peeking 問題。
7. 驗證索引狀態 (USABLE/VISIBLE)。
8. 以上皆無 → /*+ INDEX(t idx_name) */ 強制驗證索引確實可用 → 分析 CBO 成本計算差異。

五、兩大追蹤調試利器

1. EXPLAIN PLAN + AUTOTRACE

@?/rdbms/admin/utlxplan.sql  -- 建立 PLAN_TABLE
SET AUTOTRACE ON EXPLAIN
SELECT ...;

2. TKPROF 全量追蹤

-- 1. 找 SID/SERIAL#
SELECT sid, serial# FROM v$session WHERE username = 'SCOTT';

-- 2. 開啟追蹤 (需 SYS 權限)
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE);

-- 3. 執行應用流程

-- 4. 關閉追蹤
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, FALSE);

-- 5. 分析 trc 檔
tkprof ora_12345.trc output.txt EXPLAIN=scott/tiger

輸出檔包含每條 SQL 的執行計劃、CPU、邏輯/物理讀、執行時間，快速定位問題 SQL。

六、總結

索引不被使用 ≠ 索引無用。CBO 基於成本模型，在統計資訊準確、SQL 寫法正確、參數設定合理的前提下，極少做出錯誤選擇。系統化排查「統計 → SQL 寫法 → 連接/順序 → 特殊語義 → 參數/環境」五大維度，配合 AUTOTRACE/TKPROF 實證，即可解決 95% 索引失效疑難雜症。