Oracle like Optimization

Query records of % XX %

1. Use instr
Select count (*) from table t where instr (T. column, 'xx')> 0
This query is very effective and fast

 

Query records of % xx

Select count (C. c_ply_no) as Count

From policy_data_all C, item_data_all I

Where C. c_ply_no = I. c_ply_no

And I. c_lcn_no like '% 100'

During execution, the execution plan is displayed, the consumption value,IoValue,CPUThe value is not always large, becauseLikeFuzzy search causes index failure and full table Scan

 

 

Solution: This is only prefix fuzzySQLThe following code can be modified:

Select count (C. c_ply_no) as Count

From policy_data_all C, item_data_all I

Where C. c_ply_no = I. c_ply_no

AndReverse (I. c_lcn_no)LikeReverse ('% 8080')

 

Item_data_allTableC_lcn_noYou can perform fuzzy match before a field. For example:

Select *

From (select C. c_ply_no as c67_0 _,

C. c_insrnt_cnm as c68_1432_0 _,

I. c_lcn_no as c83_1432_0 _,

To_char (C. t_insrnc_bgn_tm, 'yyyy-mm-dd') as t84_1432_0 _,

C. c_edr_type as c85_1432_0 _,

C. c_prod_no as c86_1432_0 _,

C. c_inter_cde as cintercde1432_0 _

From policy_data_all C, item_data_all I

Where C. c_ply_no = I. c_ply_no

AndReverse (I. c_lcn_no)LikeReverse ('% 8080')

Order by C. c_ply_no DESC)

UseFlip Function+ LikePrefix fuzzy search+Create a flipped Function Index=The index of the flipped function does not go through full scanning. Effectively reduce the consumption value,IoValue,CPUValue, especiallyIoValue.