快捷導(dǎo)航

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

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

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

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

場景

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

環(huán)境

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

索引失效情況及驗(yàn)證

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

驗(yàn)證一：索引列為is null 和 is not null時(shí)，索引失效

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

結(jié)論一

經(jīng)驗(yàn)證索引列查詢使用is null 和 is not null則該列索引失效。

驗(yàn)證二：索引列為 !=和 <> 時(shí)會(huì)導(dǎo)致該索引列失效

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

結(jié)論二

經(jīng)驗(yàn)證索引列查詢使用 !=和 <> 時(shí)會(huì)導(dǎo)致該索引列失效

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

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

結(jié)論三

索引列用函數(shù)處理則該索引會(huì)失效,如字符串函數(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');

驗(yàn)證四：索引列使用like的前置%查詢，則該索引列失效。

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

like 使用后置百分號(hào)走索引

結(jié)論四

經(jīng)驗(yàn)證索引列使用like的前置%查詢時(shí)會(huì)導(dǎo)致該索引列失效，但是使用了ike的后置%則會(huì)走索引

驗(yàn)證五：范圍索引查詢和等值索引查詢同時(shí)存在，則范圍索引失效

其中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