利用reverse函数索引优化like ‘%xxxx‘

前言：

我们都知道在使用Like模糊查询，只有使用like 'xxxx%'格式才能正确利用索引范围扫描，那么实际中遇到按字符串末尾模糊查询（%xxxx）该如何优化？

一、构建实验例子

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

二、优化like '%xxxx’方法

1.创建reverse函数索引

create index idx_test1_n1 on test1(reverse(name));

2.等价改写原sql

select * from test1 t where reverse(t.name) like reverse('%AQPZCH');

3.对比前后性能

优化前：

优化后

结论：

优化后逻辑读下降2000倍！！！由于实验只有90M，随着表越大性能提升越明显