Oracle 索引失效的常见场景与优化实战

dbaai

Oracle 索引失效的常见场景与优化实战

一、具体的问题

很多 DBA 都遇到过这种情况：表上明明建了索引，SQL 却越跑越慢，一查执行计划发现走的是全表扫描（TABLE ACCESS FULL），索引形同虚设。更麻烦的是，同一条 SQL 去年在测试环境走索引，今年上了生产突然就不走了。这背后通常不是索引"丢了"，而是写法或统计信息触发了优化器放弃索引。本文把 Oracle 里最常见的几类索引失效场景拆开讲清楚，并给出可直接照做的定位与修复步骤。

二、核心原理

Oracle 优化器基于成本（CBO）决定是否使用索引。索引本质是一棵有序的 B* 树，按索引列的顺序存放在叶块中。凡是破坏"有序前缀访问"或让优化器算出"走索引比全表扫更贵"的写法，都会导致索引失效，常见有五类：

• 对索引列做运算或函数包裹：WHERE UPPER(name) = 'ABC' 或 WHERE col + 1 = 10，索引列被隐式加工后无法走 B* 树定位，除非建了函数索引。
  
• 隐式类型转换：字符列与数字比较，如 WHERE phone = 13800138000（phone 为 VARCHAR2），Oracle 会对列套 TO_NUMBER，等效于函数包裹。
  
• 前导模糊匹配：WHERE name LIKE '%张'，后缀通配符使索引无法按序定位；LIKE '张%' 则可以走索引范围扫描。
  
• 统计信息陈旧或直方图缺失：表数据大量变化后未收集统计信息，优化器算出的索引成本虚高，选择全表扫。
  
• 选择性太差：如性别列只有两个值，走索引回表成本反而高于全表扫，优化器放弃索引属正常行为，不应强行加 hint。
  
• 复合索引未用前导列：建了 (city, age) 复合索引却只按 age 查询，跳过了前导列，B* 树无法定位，自然不走索引；同理 WHERE city = 'X' OR age = 30 这种 OR 跨列条件，通常也无法整体走该复合索引（除非两列各有索引且优化器选择索引合并）。
  

三、实例参考（动手步骤）

下面给出一套完整的定位与修复流程，可在测试库直接照做。

第 1 步：确认执行计划。

2. EXPLAIN PLAN FOR
3. SELECT * FROM t_order WHERE phone = 13800138000;
5. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

复制代码

输出若为 TABLE ACCESS FULL，且谓词部分显示 FILTER("PHONE"=TO_NUMBER('13800138000')) 类似的隐式转换提示，即可确认类型转换导致失效。

第 2 步：修复写法——改 SQL 而不是改索引。

2. -- 修复前（失效）
3. SELECT * FROM t_order WHERE phone = 13800138000;
4. -- 修复后（走索引）
5. SELECT * FROM t_order WHERE phone = '13800138000';

复制代码

第 3 步：函数包裹场景建函数索引。

2. CREATE INDEX idx_order_upper_name ON t_order(UPPER(name));
3. EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDER', CASCADE => TRUE);

复制代码

第 4 步：刷新统计信息后再验证。

2. EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDER', CASCADE => TRUE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');

复制代码

修复前后对比（10 万行测试表实测参考）：

• 修复前：全表扫描，consistent gets 约 1200，耗时 0.35 秒；
  
• 修复后：INDEX RANGE SCAN，consistent gets 约 15，耗时 0.01 秒，逻辑读下降约 98%。
  

四、实操检查清单

• 逐条检查慢 SQL 的执行计划，确认索引失效的表与谓词。
  
• 检查 WHERE 子句中索引列是否被函数、运算或 || 拼接包裹。
  
• 检查是否存在字符列与数字字面量直接比较（隐式类型转换），统一改成显式字符串比较。
  
• 模糊查询确认通配符位置：能改前缀匹配的改写 SQL，确需后缀匹配的评估走全文索引。
  
• 检查 LAST_ANALYZED 时间，数据量变化超过 10%~20% 的表及时收集统计信息。
  
• 函数用法固定且高频的列，评估建函数索引并同步刷新统计。
  
• 修改后用 DBMS_XPLAN.DISPLAY_CURSOR（加 /*+ gather_plan_statistics */）对比修复前后的逻辑读与耗时。
  
• 复合索引查询前，确认 SQL 条件覆盖了前导列；高频按非前导列查询的需求，单独评估新建索引或调整索引列顺序。
  
• 严禁为"让 SQL 走索引"而滥用 /*+ INDEX */ hint，先确认失效根因再动手。