oracle數(shù)據(jù)庫索引失效的問題及解決

更新時間：2025年01月09日 10:07:23 作者：喜羊羊love紅太狼

本文總結了在Oracle數(shù)據(jù)庫中索引失效的一些常見場景,包括使用isnull、isnotnull、!=、<、>、函數(shù)處理、like前置%查詢以及范圍索引和等值索引同時存在等情況,通過實際的SQL查詢驗證,展示了索引失效的原因,并給出了相應的優(yōu)化建議

oracle數(shù)據(jù)庫索引失效問題

場景

在開發(fā)中有時候遇到某個表中的列明明是創(chuàng)建了索引，但查詢時卻發(fā)現(xiàn)索引失效。

環(huán)境

下面是工作流activiti中的兩張表act_hi_procinst、act_hi_taskinst關系是一對多（一個流程包含多個流程環(huán)節(jié)），一個是歷史流程表，一個是歷史流程環(huán)節(jié)表。

索引失效情況及驗證

(單表act_hi_procinst已經(jīng)在delete_reason_列上創(chuàng)建了索引 )

驗證一：索引列為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 數(shù)據(jù)庫使用索引范圍掃描方式。

這種掃描方式通過索引鍵值的范圍來定位需要的數(shù)據(jù)。

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

結論一

經(jīng)驗證索引列查詢使用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';

結論二

經(jīng)驗證索引列查詢使用 !=和 <> 時會導致該索引列失效

驗證三：索引列用函數(shù)處理則該索引會失效

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

結論三

索引列用函數(shù)處理則該索引會失效,如字符串函數(shù)trunc,to_char,substring,to_date等函數(shù)

區(qū)別下面的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 使用后置百分號走索引

結論四

經(jīng)驗證索引列使用like的前置%查詢時會導致該索引列失效，但是使用了ike的后置%則會走索引

驗證五：范圍索引查詢和等值索引查詢同時存在，則范圍索引失效

其中start_time_創(chuàng)建有普通索引，delete_reason_字段也創(chuàng)建了普通索引

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