Use like or instr in Oracle

Instr and like are all functions implemented by Oracle. In strict terms, instr is an internal function and like is an SQL standard, which is highly efficient. However, I do not know how to implement them yet. But both

Instr and like are all functions implemented by Oracle. In strict terms, instr is an internal function and like is an SQL standard, which is highly efficient. However, I do not know how to implement them yet. But both

I saw an article: Using instr in the Oracle database instead of like (see). I am very skeptical after seeing it. The following is a simple test I did to compare and verify the original article.

1. before creating an index, SQL> select count (1) from t; COUNT (1) ---------- 11905920 Elapsed: 00:00:11. 38SQL> select count (1) from t where instr (object_name, 'A')> 0; COUNT (1) ---------- 3947520 Elapsed: 00:00:10. 46SQL> select count (1) from t where object_name like '% A %'; COUNT (1) ---------- 3947520 Elapsed: 00:00:12. 31SQL> select count (1) from t where instr (object_name, 'A') = 0; COUNT (1) ---------- 7958400 Elapsed: 00:00:10. 39SQL> select count (1) from t where object_name not like '% A %'; COUNT (1) ---------- 7958400 Elapsed: 00:00:10. 94

The above results show that the efficiency of instr and like is similar when no index is created, and the efficiency of instr is slightly higher, but it is not a huge difference mentioned in this article.

2. after an index is created, SQL> create Index t_ I on t (object_name); index created. elapsed: 00:02:08. 92SQL> select count (1) from t; COUNT (1) ---------- 11905920 Elapsed: 00:00:11. 07SQL> select count (1) from t where instr (object_name, 'A')> 0; COUNT (1) ---------- 3947520 Elapsed: 00:00:12. 04SQL> select count (1) from t where object_name like '% A %'; COUNT (1) ---------- 3947520 Elapsed: 00:00:07. 33SQL> select count (1) from t where instr (object_name, 'A') = 0; COUNT (1) ---------- 7958400 Elapsed: 00:00:11. 57SQL> select count (1) from t where object_name not like '% A %'; COUNT (1) ---------- 7958400 Elapsed: 00:00:06. 47

From the test above, we can see that after adding an index, like is more efficient than instr, and takes half of instr time, which can be described as a huge difference.

Summary

Instr and like are all functions implemented by Oracle. In strict terms, instr is an internal function and like is an SQL standard, which is highly efficient. However, I do not know how to implement them yet. However, the performance difference between the two depends on the specific database environment, such as the table structure, data distribution, index, database version, and current load of the database. In addition, high efficiency also comes at the cost of database and server resource consumption. When the time is the same order of magnitude or the user permits, how to control the efficiency is the key.

From this, we can see that database performance optimization is an art that requires continuous learning and practice. In this way, we just declare the journey of database performance optimization.