oracle数据库索引失效的问题及解决_Oracle

oracle数据库索引失效问题

场景

在开发中有时候遇到某个表中的列明明是创建了索引，但查询时却发现索引失效。

环境

下面是工作流activiti中的两张表act_hi_procinst、act_hi_taskinst关系是一对多（一个流程包含多个流程环节），一个是历史流程表，一个是历史流程环节表。

索引失效情况及验证

(单表act_hi_procinst已经在delete_reason_列上创建了索引 )

验证一：索引列为is null 和 is not null时，索引失效

select * from act_hi_procinst t where t.delete_reason_ is not null;
select * from act_hi_procinst t where t.delete_reason_ is null；

全表扫描，该列索引失效

select * from act_hi_procinst t where t.delete_reason_ ='activiti_deleted' and rownum < 1000;

索引生效，oracle 数据库使用索引范围扫描方式。

这种扫描方式通过索引键值的范围来定位需要的数据。

select * from act_hi_procinst t where t.delete_reason_ is not null
and t.start_time_ between to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2023-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and rownum < 1000

结论一

经验证索引列查询使用is null 和 is not null则该列索引失效。

验证二：索引列为 !=和 <> 时会导致该索引列失效

select * from act_hi_procinst t where t.delete_reason_ !='activiti_deleted';
select * from act_hi_procinst t where t.delete_reason_ <>'activiti_deleted';

结论二

经验证索引列查询使用 !=和 <> 时会导致该索引列失效

验证三：索引列用函数处理则该索引会失效

select * from act_hi_procinst t where to_char(start_time_,'yyyy')= '2023'

结论三

索引列用函数处理则该索引会失效,如字符串函数trunc,to_char,substring,to_date等函数

区别下面的sql(下面的sql走索引)

select * from act_hi_procinst t where t.start_time_ >= to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');

验证四：索引列使用like的前置%查询，则该索引列失效。

select * from act_hi_procinst t where t.business_key_ like '%20230103-0000102'

like 使用后置百分号走索引

结论四

经验证索引列使用like的前置%查询时会导致该索引列失效，但是使用了ike的后置%则会走索引

验证五：范围索引查询和等值索引查询同时存在，则范围索引失效

其中start_time_创建有普通索引，delete_reason_字段也创建了普通索引

sql一：

select * from act_hi_procinst t where t.start_time_
between to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2023-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.delete_reason_ ='activiti_deleted'
and rownum < 1000；

sql二：

select * from act_hi_procinst t
where t.start_time_ >= to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.start_time_<= to_date('2023-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.delete_reason_ ='activiti_deleted'
and rownum < 1000