To put it simply, like Optimization

Let me give you a few simple sentences. For more information, see the example.

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'

Code:

-- '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 ......

 

Sys @ mescp> select reverse ('20140901') from dual; reverse ('20140901 ')

--------------------------------

321

 

1 row selected.

Sys @ mescp> Create Table test_like as select object_id, object_name from dba_objects;

Table created.

Sys @ mescp> Create index test_like _ name on test_like (object_name );

Index created.

Sys @ mescp> Create index test_like _ name_reverse on test_like (reverse (object_name ));

Index created.
Sys @ mescp> analyze table test_like compute statistics for table for all indexes;

Table analyzed.

Sys @ mescp> set autotrace trace exp

 

 

-- 'Like starting with a constant will use index. No problem ...... '

Sys @ mescp> select * From test_like where object_name like as % ';

Execution Plan

----------------------------------------------------------

0 SELECT statement optimizer = choose (cost = 2 card = 655 bytes = 15720)

1 0 Table Access (by index rowid) of 'test _ like' (cost = 2 card = 655 bytes = 15720)

2 1 index (range scan) of 'test _ likesponname' (NON-UNIQUE) (cost = 2 card = 118)

-- 'Both the beginning and end are %. Sorry, it is difficult to optimize'

Sys @ mescp> select * From test_like where object_name like '% ';

Execution Plan

----------------------------------------------------------

0 SELECT statement optimizer = choose (cost = 6 card = 655 bytes = 15720)

1 0 Table Access (full) of 'test _ like' (cost = 6 card = 655 ytes = 15720)

-- 'End with a constant. index cannot be applied when writing directly'

Sys @ mescp> select * From test_like where object_name like '% s ';

Execution Plan

----------------------------------------------------------

0 SELECT statement optimizer = choose (cost = 6 card = 655 bytes = 15720)

1 0 Table Access (full) of 'test _ like' (cost = 6 card = 655 bytes = 15720)

-- 'End with a constant, add a reverse function, and you can use the Index'

Sys @ mescp> select * From test_like where reverse (object_name) like reverse ('% ');

Execution Plan

----------------------------------------------------------

0 SELECT statement optimizer = choose (cost = 2 card = 655 bytes = 15720)

1 0 Table Access (by index rowid) of 'test _ like' (cost = 2 card = 655 bytes = 15720)

2 1 index (range scan) of 'test _ like1_name_reverse '(NON-UNIQUE) (cost = 2 card = 118)