使用instr 函數優化替換Like條件子句提高資料檢索性能案例總結

在業務上有個性能較低的SQL語句，使用了Like條件子句進而導緻其它内關聯表索引失效，如下SQL:

select count(1) countNumber from(
select cc.CLAIM_ID,ROWNUM from claimcontract cc
INNER JOIN GNTSLIP_EXPAN_INF G ON CC.Contract_Id=G.ARRANGEMENT_ID
inner join (select C.CONTRACT_ID from contract c where exists( select 1 from CONTRACTROLE CR
                where CR.CONTR_COMPONENT_ID = C.CONTRACT_ID and CR.CONT_ID = '' and C.END_DT < SYSDATE AND

                CR.END_REASON_TP_CD IS NULL AND (CR.CONTR_ROLE_TP_CD BETWEEN 1 AND 3)
) AND C.EXECUTED_DT>=ADD_MONTHS(trunc(SYSDATE), -36)) t on cc.contract_id=t.CONTRACT_ID
AND (G.GNTSLIP_NO like 'E%' or G.GNTSLIP_NO like 'W%' or G.GNTSLIP_NO like 'P%' )
)
           

優化前的SQL執行計劃：

使用instr 函數優化替換Like條件子句優化後SQL語句：

select count(1) countNumber from(
select cc.CLAIM_ID,ROWNUM from claimcontract cc
INNER JOIN GNTSLIP_EXPAN_INF G ON CC.Contract_Id=G.ARRANGEMENT_ID
inner join (select C.CONTRACT_ID from contract c where exists( select 1 from CONTRACTROLE CR
                where CR.CONTR_COMPONENT_ID = C.CONTRACT_ID
                and CR.CONT_ID = '11111111111111'
                and C.END_DT < SYSDATE
                AND CR.END_REASON_TP_CD IS NULL
                AND (CR.CONTR_ROLE_TP_CD BETWEEN 1 AND 3)
) AND C.EXECUTED_DT>=ADD_MONTHS(trunc(SYSDATE), -36)) t on cc.contract_id=t.CONTRACT_ID
AND (instr(G.GNTSLIP_NO,'E')=1 or instr(G.GNTSLIP_NO,'W')=1 or instr(G.GNTSLIP_NO,'P')=1 )
)
           

優化後的SQL執行計劃：

經過對比優化前和優化後的SQL執行計劃，可以很明顯的看出性能方面有很明顯的差别，優化前的SQL所使用的Like子句中并沒有用上索引，導緻SQL查詢時全表掃描；優化後的SQL采用了instr函數來替代模糊查詢，使資料庫表索引生效，性能上有了非常明顯的提升。