The difference between Oracle like and InStr
InStr does not belong to a fuzzy query.
From the standpoint of efficiency:
Who can use the index, whose query speed will be fast.
Like sometimes you can use the index, for example: Name "Lee%"
The index is invalidated when the following conditions occur: name like '% Lee '
Unlike other databases, Oracle supports functional indexing. For example, build a InStr index on the name field. The query speed is relatively fast.
There are nearly 11 million data in the T table, and many times we want to do string matching, in SQL statements, we usually use like to achieve our search goals. But the actual test shows that the efficiency of like is quite different from the InStr function. Here are some test results:
Sql> Set Timing on
Sql> Select COUNT (*) from T where InStr (title, ' manual ') >0;
COUNT (*)
----------
65881
elapsed:00:00:11.04
Sql> Select COUNT (*) from T where the title like '% ' manual% ';
COUNT (*)
----------
65881
elapsed:00:00:31.47
Sql> Select COUNT (*) from T where InStr (title, ' manual ') = 0;
COUNT (*)
----------
11554580
elapsed:00:00:11.31
Sql> Select COUNT (*) from T where title isn't like '% manual% ';
COUNT (*)
----------
11554580 In addition, I am in the knot of another 200 million tables, using 8 parallel, using like query for a long time does not come out results, but the use of instr,4 minutes to complete the lookup, performance is quite good. These tips are good to use and work more efficiently. The above tests show that some of the functions built in Oracle are optimized to a considerable degree.