Oracle索引失效的几种常见情况

Oracle索引失效的几种情况

Oracle 索引的目标是避免全表扫描，提高查询效率，但有些时候却适得其反。

oracle 索引有一些限制条件，如果你违反了这些索引限制条件，那么即使你已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差。 下面就是总结的能使 Oracle 索引失效的限制条件。

1. 没有 where 子句

2. 使用 is null 和 is not null

3. 对索引字段进行了运算或者使用了函数

   select * from test t where id + 1 = 100; //这种情况索引失效
   select * from test t where id = 100 - 1; //这种情况索引生效
   
   select * from test t where abs(id)= 100; //索引失效
   

4. 查询中的数据类型和字段类型不一致（默认函数转化）

   select * from test t where col = 100;  //col 是varchar2 类型，这种情况能查出来，但索引不生效。因为隐性使用了 to_number(col) = 100;
   

5. 违反了索引的最左匹配原则

6. 使用 LIKE ‘%T’ 进行模糊查询（%在前面）

7. 优化器自动分析全表扫描更快

   // 优化器会分析走索引和全表扫描的情况，哪一个更快
   // 例如只有10000条数据，去查询 > 100 的条件，直接全表扫描更快，就不会使用索引
   // 或者只有10000条数据，去查询 <> 100 的条件，要去对比10000条数据，不如直接全面扫描
   

8. WHERE 子句中使用不等于操作

   不等于操作包括：<>, !=, NOT colum >= ?, NOT colum <= ?

   对于这个限制条件可以通过 OR 替代，例如： colum <> 0 ===> colum>0 OR colum<0

   这其实也是第7种的一种表现方式。