Like vs instr efficiency 2010-12-03 16:59 The table has nearly 11 million data, many times we want to do string matching, in the SQL statement, 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.
[Excerpt from]http://hi.baidu.com/xublog/blog/item/86b7c0e7f0901b3fb93820cb.html