Summary of like, instr, and regexp_like performance in Oracle

1. regexp_like is powerful, but its efficiency is worse than like;

2. If no index is available for the like field and % is available before and after matching the string, the instr efficiency is high;

3. If the like field is indexed and both sides of the matched string are %, the like efficiency is high if only the index field is operated. If there are other fields, the instr efficiency is high, the possible cause is that the full index scan is used instead of full table scan for only indexed fields;

4. If the like field is indexed and only % is matched at the end of the string, the like efficiency is high because like uses the index, however, instr will cause Index failure and table scanning;

To sum up, the efficiency of like and instr depends on the situation. The efficiency of both is higher than that of regexp_like.

[Experiment]

Note: This experiment is transferred from the network, but the original source cannot be verified, so no reference link is added. If there is any infringement, please contact me.

For example, if the t table contains nearly 11 million data, we need to perform String Matching. Tests show that the performance of the like function differs from that of the instr function. The test result is as follows:

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

The other 0.2 billion-plus tables use eight parallel queries and the like query does not return results for a long time, but the instr function is used for 4 minutes to complete the search. The performance is quite good. These tips are well used and greatly improve work efficiency.

Note: The instr (title, 'AAA')> 0 function is equivalent to like, instr (title, 'AAA') = 0 function is equivalent to not like, but the efficiency is different.

If a non-unique index is added to the title field:

Create index idx_t _ title on t (title );

In this way, use

Select * from t where instr (title, 'manual')> 0;

The efficiency of such statement query can be improved a lot. The more the table data volume, the more obvious the instr advantage.

If other fields are not included:

Select count (1) from t where title like '% manual % ';

Select count (1) from t where instr (title, 'manual')> 0;

The result shows that the like is much more efficient than instr.