LIKE statement Optimization in Oracle

1. Try not to use like '%'

2. For like '%' (not starting with %), Oracle can apply the index on colunm

3. For like '%... '(Not ending with %), you can change it to like' % 'in the form of reverse + function index'

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.

Create table test_like as select object_id, object_name from dba_objects;

------- Create a test table

Create index test_like _ name on test_like (object_name );

------ Create an index

Create index test_like _ name_reverse on test_like (reverse (object_name ));

------ Create reverse Index

Analyze table test_like compute statistics for table for all indexes;

------ Analyze a table

When SQLPLUS is used to connect to data, it must be SQLPLUS, because the following write commands are not supported in PLSQL command lines;

Set autotrace trace exp

----- Set SQL tracking

Set linesize 2000

------- Set the output width

Select * from test_like where object_name like 'as % ';

Index Used

Select * from test_like where object_name like '% s ';

No index used

Select * from test_like where reverse (object_name) like reverse ('% ');

Index Used