了解SQL的同学,都知道,like关键字可以走索引,只要字符串不是以通配符(%)开始。
如果类似 like "%xxx" 的sql,如何走索引呢?基于REVERSE()函数来创建一个函数索引。
1、准备数据:
CREATE TABLE jka AS SELECT ROWNUM id, dbms_random.string('x',10) v FROM dual CONNECT BY LEVEL <= 10000;2、创建原始索引:
CREATE INDEX jka_normal ON jka (v); BEGIN dbms_stats.gather_table_stats (USER ,'JKA' ,estimate_percent=>100 ,cascade=>TRUE); END;3、以下SQL基于jka_normal索引走范围扫描:
SELECT * FROM jka WHERE v LIKE 'ABC%';4、但是下面的SQL将走全表扫描(不会使用索引):
SELECT * FROM jka WHERE v LIKE '%ABC';5、现在,创建一个函数索引(不要与反向索引[ REVERSE INDEX]混淆):
CREATE INDEX jka_reverse ON jka(REVERSE(v)); BEGIN dbms_stats.gather_table_stats (USER ,'JKA' ,estimate_percent=>100 ,cascade=>TRUE); END;6、下面的SQL将基于 jka_reverse索引走范围扫描:
SELECT * FROM jka WHERE REVERSE(v) LIKE REVERSE('%ABC');
问题解决。
本文翻译自
https://jeffkemponoracle.com/2008/01/17/like-with-wildcard-at-start-can-use-an-index/