oracle 索引失效

在 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：检查索引统计信息。