在 Oracle 11g 中，索引失效的常見原因包括函數修改列、隱式類型轉換、統計信息過時等，解決方法需結合版本特性（如虛擬列、索引跳躍掃描）。通過執行計劃分析、統計信息維護和合理使用提示（Hints），可有效優化索引使用。對于關鍵業務 SQL，建議定期監控并綁定執行計劃（SQL Plan Management）

1. 索引列被函數或表達式修改

• 原因：對索引列使用函數或表達式（如?UPPER(column)、column + 1）會導致索引無法匹配。

SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYYMMDD') = '20231001';

解決方法：

函數索引：為函數或表達式創建專用索引：

CREATE INDEX idx_hire_date_str ON employees(TO_CHAR(hire_date, 'YYYYMMDD'));

2. 復合索引未使用前導列

?

• 原因：復合索引?(col1, col2)?必須使用?col1?才能生效，否則可能失效。

• Oracle 11g 優化：支持?索引跳躍掃描（Index Skip Scan），即使未指定前導列，也可能通過跳躍掃描使用索引（但效率較低）。

-- 索引為 (department_id, employee_id)
SELECT * FROM employees WHERE employee_id = 100; -- 可能觸發跳躍掃描?

3. 隱式類型轉換

• 原因：查詢條件與索引列數據類型不匹配（如字符串 vs 數字）。

-- 索引列 employee_id 是 NUMBER 類型
SELECT * FROM employees WHERE employee_id = '100'; -- 隱式轉換為字符串

4. 統計信息過時

• 原因：Oracle 優化器依賴統計信息計算成本，過時統計信息導致誤判。

• Oracle 11g 特性：

  • 自動統計信息收集任務（GATHER_STATS_JOB）默認開啟，但可能因業務負載被禁用。

  • 新增增量統計信息收集，適用于分區表。

解決方法：

• 手動收集統計信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMPLOYEES', cascade => TRUE);

檢查自動任務狀態：

SELECT * FROM DBA_AUTOTASK_CLIENT WHERE TASK_NAME = 'auto optimizer stats collection';

5. 高比例數據返回

• 原因：當查詢返回超過約 15-20% 的數據時，優化器可能選擇全表掃描。

• 解決方法：

使用?INDEX?提示強制使用索引（需謹慎）：

?SELECT /*+ INDEX(employees idx_salary) */ * FROM employees WHERE salary > 5000;

6. 使用?!=?或?NOT IN?操作符

?

• 原因：非等值查詢可能導致優化器跳過索引。

SELECT * FROM employees WHERE status != 'ACTIVE';

解決方法：

?改寫為?OR?條件或使用?INDEX_FFS（快速全索引掃描）

SELECT * FROM employees WHERE status = 'INACTIVE' OR status = 'PENDING';

7. 索引被標記為 UNUSABLE

• 原因：直接路徑加載（如?INSERT /*+ APPEND */）或分區維護后，索引可能失效。

檢查與修復：

-- 檢查索引狀態
SELECT index_name, status FROM user_indexes WHERE table_name = 'EMPLOYEES';

-- 重建索引
ALTER INDEX idx_emp_name REBUILD;

8.?LIKE?以通配符開頭

• 原因：LIKE '%abc'?無法利用 B-Tree 索引。

解決方法：

• 反向鍵索引（Reverse Key Index）：

CREATE INDEX idx_name_reverse ON employees(REVERSE(name));
SELECT * FROM employees WHERE REVERSE(name) LIKE REVERSE('%son');?

9. OR 條件導致索引失效

• 原因：多個?OR?條件未命中聯合索引時，觸發全表掃描。

解決方法：

?SELECT * FROM employees WHERE dept_id = 10
UNION ALL
SELECT * FROM employees WHERE salary > 10000;

10. 參數設置影響索引選擇

• 關鍵參數：

  • OPTIMIZER_INDEX_COST_ADJ：調整索引訪問成本（默認 100），降低該值可能讓優化器更傾向索引。

  • OPTIMIZER_INDEX_CACHING：影響嵌套循環連接中索引的緩存成本。

• 操作建議：

  • 非必要不要修改全局參數，可通過提示（Hint）臨時調整

?SELECT /*+ OPT_PARAM('optimizer_index_cost_adj', '50') */ * FROM employees WHERE ...;

?11. 位圖索引的并發問題

?

• 原因：位圖索引在并發 DML 時易被鎖定，導致性能下降或失效。

• Oracle 11g 優化：支持更多位圖索引維護策略，但仍需謹慎使用。

• 解決方法：

  • 在高并發 OLTP 環境中避免使用位圖索引。

  • 使用 B-Tree 索引替代。

12. 索引列允許 NULL 值

• 原因：B-Tree 索引不存儲全 NULL 的條目，IS NULL?查詢無法使用索引。

• 解決方法：

  • 創建函數索引：

CREATE INDEX idx_comm_null ON employees(NVL(commission_pct, -1));
SELECT * FROM employees WHERE NVL(commission_pct, -1) = -1;

Oracle 11g 特有工具與診斷方法

執行計劃分析：

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

SQL 調優顧問（SQL Tuning Advisor）：

?-- 生成調優任務
DECLARE
? l_task VARCHAR2(64);
BEGIN
? l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'SELECT * FROM employees WHERE ...');
? DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task);
END;
/
-- 查看建議
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_name') FROM dual;

?

動態性能視圖?

• V$SQL_PLAN：查看實際執行計劃。

• DBA_IND_STATISTICS：檢查索引統計信息。