Oracle like query

Query method: Like '%xx% '

Normal: SELECT * from TABLE T WHERE t.colunm like '%xx% '

Optimization: Using INSTR

SELECT * from TABLE T WHERE INSTR (t.colunm, XX) >0

At the time of execution, the execution plan shows that the consumption value, IO value, CPU value are very large, because the fuzzy query in front of like after causes the index to fail, the full table scan.

1. Try not to use like '% '

2. For like '% ' (not starting with%), Oracle can apply the index on the COLUNM

3. For like '% ... ' (Does not end with%), can be used reverse + function index form, change into like '% '

4. Use the Oracle intrinsic function: INSTR () to resolve non-use like '% '.

Appendix:

InStr (string1, string2[,start_position[,nth_appearence]])

String1: To find in this string.

String2: The string to find in string1.

Start_position: The location to look for from string1. Optional, default is 1, positive numbers are retrieved from left to right, negative numbers are retrieved from right to left.

Nth_appearence: Find the first few occurrences of string2. Optional, default is 1, cannot be negative.

Note: If not found, returns 0.

Therefore, in the above example,

INSTR (T.COLUNM, ' xx ') >0, is Like;instr (T.COLUNM, ' xx ') = 0, then it is not.

Oracle like query