The T table has nearly 11 million data records. In many cases, we need to perform string matching. in SQL statements, we usually use like to achieve our search goal. However, the actual test shows that the like efficiency is quite different from the instr function. The following 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 not like '% manual % ';
Count (*)
----
11554580
In addition, I used eight parallel queries in another 0.2 billion-plus tables and used like to query the tables for a long time. However, I used instr to complete the search in four minutes, the performance is quite good. These tips are well used and greatly improve work efficiency. According to the test above, some built-in Oracle functions have been optimized to a considerable extent.