like‘%xxxxx%‘性能优化

前言：

like使用方式一共三种格式
like ‘xxxx%’——这种能正常使用索引
like ‘%xxxx’——利用reverse函数索引优化
like’%xxxx%’——使用客制化函数instr和通用手段两种方法

一、构建实验条件

1.创建测试表（大概90M）

create table test1 as
select level id,
       dbms_random.string('X', 30) name,
       dbms_random.string('f', 30) address,
       dbms_random.string('x', 80) info
  from dual
connect by level <= 500000;

2.收集统计信息

begin
  dbms_stats.gather_table_stats('SCOTT', 'TEST1');
end;
/

3.需要优化的sql

SQL> select * from test1 t where t.name like '%AQPZCH%';

        ID NAME                                                                             ADDRESS                                                                          INFO
---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 S0HHEJJ8OXPSW2UMA4BTXT24AQPZCH                                                   LWOXKGPPZWFDBBLAFZKGLJYLECTFMT                                                   1Z38U41HOTII8EVDU94O06DAXLYA3WZML23193J3UPINNCRSP9RRDV0YK1IX1049WLJIIFGOZMCNI43N

4.原执行计划和逻辑读

二、使用客制化函数instr优化

1.创建instr索引

create index idx_test1_n2 on test1(instr(name,'AQPZCH'));

2.等价改写原sql

select * from test1 t where instr(t.name,'AQPZCH') >0;

3.查看执行计划和逻辑读信息

三、通用优化手段

1.创建普通索引

create index idx_test1_n3 on test1(name);

2.等价改写原sql

select /*+no_merge(b) use_nl(t b)*/
 t.*
  from test1 t, (select rowid from test1 a where a.name like '%AQPZCH%') b
 where t.rowid = b.rowid;

3.查看执行计划和逻辑读

结论：

instr函数优化特点：
优点：能使用索引范围扫描，最大程度提升性能（此例子提升2000+倍）
缺点：使用场景不广泛，针对特定sql有奇效
通用优化特点：
优点：任何场景都可以应用，效果很不错（此例子提升4倍，随着表越大效果越显著）
缺点：在特定场景效果没有instr函数索引显著。