Oracle 索引失效的常见场景与优化实战
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 步：确认执行计划。
EXPLAIN PLAN FOR
SELECT * FROM t_order WHERE phone = 13800138000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
输出若为 TABLE ACCESS FULL，且谓词部分显示 FILTER("PHONE"=TO_NUMBER('13800138000')) 类似的隐式转换提示，即可确认类型转换导致失效。
第 2 步：修复写法——改 SQL 而不是改索引。
-- 修复前（失效）
SELECT * FROM t_order WHERE phone = 13800138000;
-- 修复后（走索引）
SELECT * FROM t_order WHERE phone = '13800138000';
第 3 步：函数包裹场景建函数索引。
CREATE INDEX idx_order_upper_name ON t_order(UPPER(name));
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDER', CASCADE => TRUE);
第 4 步：刷新统计信息后再验证。
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，先确认失效根因再动手。
页:
[1]