Simple like optimization in Oracle databases-Author: rollingpig

Let's put it simply. For details, see Example 1. Try not to use like '% .. %' 2. For like '... %...' (not starting with %), Oracle can apply index 3 on colunm. For like '%...' (not ending with %), you can change it to like '... %' in the form of reverse + function index'

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)