一般情況下飘弧，sql中使用col_name like 'ABC%‘的情況才能使用到col_name字段上的索引若专。那么如果是col_name like '%ABC%'的情況玉吁，能否使用索引呢锉桑？
答案是：可以使用索引铺厨，但是需要改寫SQL并創(chuàng)建reverse函數索引陪白。
具體如何實現颈走？聽專家為你揭曉。
一咱士、col_name like '%ABC'時的優(yōu)化方法
Test case：
? ? Create table t1 as select * from dba_objects;
????Create index idx_t1_objectname1 on t1(object_name);
在正常情況下立由，百分號在后面，可以使用索引：
select object_name from t1 where object_name like ‘DBA%';
百分號在前面序厉，不能使用索引：
select object_name from t1 where object_name like '%LIB';
解決方法
create index idx_t1_objectname2 on t1(reverse(object_name));
select object_name from t1 where?reverse(object_name)?like?reverse('%LIB');
我們看執(zhí)行計劃：
改寫后SQL走了索引锐膜。
二、col_name like '%ABC%'時的優(yōu)化方法
一般認為這種情況是不能使用索引的弛房，但還是有一些優(yōu)化方法可以使用道盏。
有三種情況：
1、ABC始終從字符串開始的某個固定位置出現文捶，可以創(chuàng)建函數索引進行優(yōu)化
2荷逞、ABC始終從字符串結尾的某個固定位置出現，可以創(chuàng)建函數組合索引進行優(yōu)化
3粹排、ABC在字符串中位置不固定种远，可以通過改寫SQL進行優(yōu)化
情況1、先創(chuàng)建substr函數索引顽耳，再使用like ‘ABC%’坠敷。
假如ABC從字符串第五位出現：
Test Case：
create index idx_substr_t1_objname on t1 (substr(object_name,5,30));
select object_id,object_type,object_name from t1
where substr(object_name,5,30) like 'TAB%';
情況2妙同、先創(chuàng)建reverse+substr組合函數索引，再使用like reverse‘%ABC’膝迎。
假如ABC從字符串倒數第五位出現：
Test Case：
Create index idx_t1_reverse2 on t1(reverse(substr(object_name,1,length(object_name)-4)));
select object_id,object_name,object_type from t1?
where reverse(substr(object_name,1,length(object_name)-4)) like reverse('%TAB_COL');
情況3粥帚、這種情況需要like的字段上存在普通索引，主要在SQL的寫法上做改進限次。
原來的SQL是這樣寫的：
Select object_id,object_type,object_name from t1
where object_name like '%ABC%‘;
改寫后的SQL是這樣的：
Select object_id ,object_type,object_name from t1?
Where object_name in
(select object_name from t1 where object_name like ‘%ABC%’);
Test Case：
create index idx_t1_object_name on t1 (object_name);
Select object_id,object_type,object_name from t1
where object_name like '%TABCOL%';
此時SQL的執(zhí)行計劃是t1 表做全表掃描茎辐。
Select object_id,object_type,object_name from t1
Where object_name in
(select object_name from t1 where object_name like '%TABCOL%');
改寫后的SQL執(zhí)行計劃是索引全掃描加索引回表操作：
優(yōu)化原理
用索引全掃描取代表的全掃描。因為索引全掃描的代價是全表掃描的1/N (即索引塊數與數據塊數的比例）掂恕，表越大，優(yōu)化效果越明顯弛槐。
改寫后SQL的執(zhí)行計劃懊亡，根據索引再回表的代價要看符合條件的記錄數多少：如果in子查詢返回的記錄數很少，那么優(yōu)化的效果就相當于效率提高了N倍乎串；如果in子查詢返回的記錄數較多店枣，兩種SQL的性能區(qū)別就不是很明顯了
