Oracle 索引失效三大陷阱：導致優化器放棄索引的 SQL 寫法與優化對策

前言：索引建得再好，用不上也是零

優化器基於成本模型 (CBO) 決定訪問路徑。即使存在完美索引，特定 SQL 寫法會導致優化器判定「全表掃描成本更低」或「無法使用索引」。本文剖析三大核心陷阱，提供改寫模式與驗證方法。

陷阱一：複合索引最左前綴原則違背

複合索引 IDX_TBL(F1, F2, F3) 的 B-Tree 結構按 F1 → F2 → F3 排序。WHERE 條件必須包含 最左列 F1 才能觸發索引範圍掃描。

對策：調整索引列順序、創建獨立索引、使用 Index Skip Scan (10g+，基數低時)。

陷阱二：索引列參與運算/函數/隱式轉換

任何形式的列操作會導致優化器無法利用索引順序特性：

-- 典型失效寫法
WHERE TO_DATE(update_date) >= TO_DATE('2001-07-18','YYYY-MM-DD')  -- 函數
WHERE UPPER(username) = 'ADMIN'                                     -- 函數
WHERE salary + bonus > 10000                                        -- 表達式
WHERE SUBSTR(id,1,3) = '001'                                        -- 函數
WHERE user_id = '12345'  -- user_id 是 NUMBER，發生隱式 TO_NUMBER 轉換

改寫模式：

• 函數下推：WHERE update_date >= TO_DATE('2001-07-18','YYYY-MM-DD')
• 函數索引 (FBI)：CREATE INDEX idx_upper_user ON users(UPPER(username))
• 虛擬列 + 索引 (11g+)：ADD (upper_user GENERATED ALWAYS AS (UPPER(username)) VIRTUAL)
• 顯式轉換：WHERE user_id = TO_NUMBER('12345') 或綁定變量正確類型

陷阱三：否定/不等/模糊/NULL 等操作符特性

以下操作符會 顯式阻止 索引範圍掃描 (除非配合特殊索引/提示)：

驗證利器：EXPLAIN PLAN 與 SQL Monitoring

1. 計劃解析：

   EXPLAIN PLAN FOR SELECT ...;
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

   關注 Operation=TABLE ACCESS FULL vs INDEX RANGE SCAN、Cost、Cardinality。
2. 實時監控 (11g+)：

   SELECT * FROM V$SQL_MONITOR WHERE SQL_TEXT LIKE '%...%';

   查看 IO_INTERCONNECT_BYTES、ELAPSED_TIME、PLAN_LINE_ID 實際執行路徑。
3. 綁定變量窺探與適應性游標共享：注意 BIND_AWARE、BIND_SENSITIVE 標記對計劃穩定性的影響。

實戰優化清單

1. 收集高質量統計信息：DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')
2. 建立函數索引/虛擬列索引覆蓋高頻函數查詢
3. 複合索引列序遵循「等值優先、範圍次之、選擇性高者前置」
4. 避免隱式轉換：應用層綁定變量類型與表定義一致
5. 模糊查詢前導通配符場景引入 Oracle Text / Elasticsearch
6. 大表優先考慮分區 + 本地索引，實現分區剪枝
7. 定期運行 DBMS_STATS.GATHER_SCHEMA_STATS、DBMS_STATS.DELETE_TABLE_STATS 清理失效統計

結語

索引優化是統計信息、SQL 寫法、索引結構、優化器特性四方博弈的動態平衡。建立「寫 SQL → EXPLAIN → 基線測試 → 上線 → 監控 → 迭代」閉環，方能讓索引持續發揮價值。