2019.利用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，随着表越大性能提升越明显
