Oracle like '%... %' optimized

Oracle like '%... %' optimized 1. Try not to use like '%' 2. For like '%' (not starting with %), Oracle can apply index3 on colunm. For like '%... '(Not ending with %), you can change it to like' % '4 in the form of reverse + function index. if you do not use like '%', use the Oracle internal function: INSTR. Create a test table and Index. Note that the focus is on the function index with reverse. At the same time, you must use cbo to create table test_like as select object_id, object_name from dba_objects; ------- create index test_like _ name on test_like (object_name ); ------ create index test_like _ name_reverse on test_like (reverse (object_name); ------ create a reverse index analyze table test_like compute statistics for table for all indexes; ------ SQL plus is used for table analysis. It must be SQLPLUS because the following write commands are not supported in PLSQL command lines. set autotrace trace exp- ---- Set SQL trace set linesize 2000 ------- set output width select * from test_like where object_name like 'as % '; select * from test_like where object_name like' % s' is used '; no index is used. select * from test_like where reverse (object_name) like reverse ('% as'); index 4 is used. fuzzy query is performed on large tables. Generally, LIKE '%' is used. However, this feature uses a full table scan. If the data volume is very large, the efficiency is extremely slow. Therefore, we try to use the ORACLE function INSTR. The tutorial procedure is as follows: first construct a table with millions of rows. SQL> insert into emp2 select * from emp2; 1032192 rows inserted as shown above, after the construction is complete, multiple LIKE queries are used consecutively to fuzzy match SQL> select * from emp2 where job like '% RE %' and ename like '% A %' and mgr like '% 3% '; empno ename job mgr hiredate sal comm deptno ----- ---------- --------- ----------- --------- ------ Executed in 1.859 seconds as shown above, LIKE to perform a full table scan once, the efficiency is very slow, the same effect. Now I want to change the INSTR function to execute SQL> select * from emp where ins Tr (job, 'Re')> 0 and instr (ename, 'A')> 0 and instr (mgr, '3')> 0; empno ename job mgr hiredate sal comm deptno ----- ---------- --------- ----------- --------- ------ Executed in 0.063 seconds. The time difference is obvious. INSTR is Executed in an instant, because this is the search field, rather than the full table scan, it seems that the internal function efficiency of oracle is still higher. Therefore, you will encounter the same problem later. In addition to full-text retrieval, this is also a good way to note: select id, name from users where instr (id, '123')> 0; equivalent to select id, name from users where id like '% 100'