使用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函数来替代模糊查询，使数据库表索引生效，性能上有了非常明显的提升。