Oracle-like and InStr performance competition

For example: There are nearly 11 million data in the  t table, many times we want to do string matching, in SQL statements, we usually use like to achieve our search goals. However, the actual test found that the efficiency of like differs considerably 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 Strong>instr (title, ' Handbook ') = 0;
  count (*)
----------
11554580
elapsed:00:00:11.31
sql> Select COUNT (*) from T where Title not like '% manual% ';
  count (*)
----------
11554580
Another more than 200 million tables, using 8 concurrent, using the like query for a long time does not come out results, but use InStr , 4 minutes to complete the lookup, the performance is quite good. These tips are good to use and work more efficiently. The above tests show that some of the functions built into ORACLE are optimized to a considerable degree.  

Editor's note: InStr (title, ' AAA ') >0 function equivalent to LIKE,INSTR (title, ' AAA ') =0 function is equivalent to not, but the efficiency is different.

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 the 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.