Go Similar and InStr fuzzy query performance in Oracle

InStr (title, ' manual ') >0 equivalent to title like '% manual% '

InStr (Title, ' Handbook ') =1 equivalent to title like ' Handbook% '

InStr (title, ' manual ') =0 equivalent to title not a like '% manual% '

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 reach our search target. However, 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 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 is not a like '% manual% ';

COUNT (*)
----------
11554580

In addition, I am in the knot of another 200 million more tables, using 8 parallel, using like query for a long time will not come out results, but using instr,4 minutes to complete the search, performance is quite good. These tips are good to use and work much better. The above test shows that some of the functions built by Oracle are optimized to a considerable extent.

InStr (title, ' AAA ') >0 equivalent to Like

InStr (title, ' AAA ') =0 equivalent to not

Special usage:

Select ID, name from users where InStr (' 101914, 104703 ', id) > 0;
It is equivalent to
Select ID, name from users where id = 101914 or id = 104703;

Improve the efficiency of fuzzy queries using Oracle's InStr function and index mates

In general, in the Oracle database, we use the following two ways to make a fuzzy query on the name field of the TB table:
1.select * from TB where name is like '%xx% ';
2.select * from TB where InStr (name, ' XX ') >0;

If there is no index on the name field, the efficiency is almost the same, basically no difference.

To increase efficiency, we can add a non-unique index to the name field:
CREATE INDEX Idx_tb_name on TB (name);

This way, then use

SELECT * from TB where InStr (name, ' XX ') >0;

Such a statement query, the efficiency can be improved a lot, the larger the amount of table data, the greater the difference. But also take into account that the Name field is indexed after the DML statement causes the index data to reorder the effect.

Go Similar and InStr fuzzy query performance in Oracle