Example Analysis of Fuzzy query performance of Like and Instr in Oracle

This article mainly introduces the Oracle Like and Instr fuzzy query performance example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

Instr (title,' manual) > 0 is equivalent to title like'% manual% 'instr (title,' manual') = 1 is equivalent to title like 'manual%' instr (title,' manual') = 0 is equivalent to title not like'% manual%'

There are nearly 11 million data in the t table, and most of the time, we have to do string matching, and in SQL statements, we usually use like to achieve our search goal. But through the actual test, it is found that the efficiency of like is quite different from that of instr function. Here are some test results:

SQL > set timing on SQL > select count (*) from t where instr (title,' Handbook') > 0; COUNT (*)-65881 Elapsed: 00from t where instr 11.04 SQL > select count (*) from t where title like'% Handbook%'; COUNT (*)-65881 Elapsed: 00VOLV 31.47 SQL > select count (*) from t where instr (title,' Handbook') = 0 COUNT (*)-11554580 Elapsed: 00 SQL > select count (*) from t where title not like'% manual%'; COUNT (*)-11554580

In addition, I am summing up another table of more than 200 million, using 8 parallelism, using like query without results for a long time, but using instr,4 to complete the search in minutes, the performance is quite good. If these tips are used well, the work efficiency will be improved a lot. The above tests show that some of the built-in functions in ORACLE have been optimized to a considerable extent.

Instr (title,'aaa') > 0 equals like instr (title,'aaa') = 0 equals not like

Special usage:

Select id, name from users where instr ('101914, 104703 percent, id) > 0

It's equivalent to

Select id, name from users where id = 101914 or id = 104703

Using instr function of Oracle and index to improve the efficiency of fuzzy query

Generally speaking, in the Oracle database, we fuzzy query the name field of the tb table in the following two ways:

Select * from tb where name like'% XX%'; select * from tb where instr (name,'XX') > 0

If there is no index on the name field, the efficiency of the two is about the same, and there is basically no difference.

To improve efficiency, we can add a non-unique index to the name field:

Create index idx_tb_name on tb (name)

In this way, use the

Select * from tb where instr (name,'XX') > 0

The efficiency of this kind of sentence query can be improved a lot, and the greater the amount of table data, the greater the difference between the two. However, it is also important to take into account the impact that the DML statement will reorder the index data after the name field is indexed.

Thank you for reading this article carefully. I hope the article "sample Analysis of Fuzzy query performance of Like and Instr in Oracle" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!