1.没有 where 子句
2.使用 like '%T' 进行模糊查询
例: select * from t_owners where name like '%李'
索引不失效: select * from t_owners where name like '张%'
3.使用 is null和 is not null 针对null值查询,索引无效
例:select * from t_pricetable where max is null;
4.where子句中使用不等于操作 例:<> != not in
例:select * from t_account where money <>100;
可以使用:select * from t_account where money>100 or money<100;
5.where子句中使用函数
例:select * from t_owners where round(id) > 5
可以使用: create index idx_owners_id on t_owners(round(id)); --建立函数索引
select * from t_owners where round(id) > 5
6.隐式转换时索引失效 (比较时类型不匹配)
例:select * from t_account where year = 2012
oracle在执行时,会把以上语句变为:select * from t_account where to_number(year) = 2012
正确写法:select * from t_account where year = '2012';
7.对索引列进行运算时导致索引失
例:select * from t_owners where id-1=5;
正确的写法:select * from t_owners where id=6;