Oracle-like and InStr Fuzzy query performance competition __oracle

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

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

InStr (Title, ' Handbook ') =0 equivalent to title not 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 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 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.

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;

improving the efficiency of fuzzy query by using Oracle's InStr function and index coordination

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

If the name field is not indexed, the efficiency of the two is almost the same, there is no difference.

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

In this way, then use

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

Such statements query, the efficiency can be improved a lot, the larger the amount of table data, the difference between the two. However, it also takes into account the effect of a DML statement that causes the index data to be reordered after the name field is indexed.