A tester for our client raised a problem where searches like ‘ABC%’ would perform satisfactorily, but searches like ‘%ABC’ would time out. Of course, the reason is that the index on the column cannot be used if a wildcard is at the start of the string, so a full table scan is always performed.
I think we’re going to accept the slow response for now, but a possible solution that we might consider in future is to use a function-based index on the REVERSE() function. Its drawback is that it requires modifying the code.
First, some test data:
CREATE TABLE jka AS SELECT ROWNUM id, dbms_random.string('x',10) v FROM dual CONNECT BY LEVEL <= 10000;
Now, create an ordinary index:
CREATE INDEX jka_normal ON jka (v); BEGIN dbms_stats.gather_table_stats (USER ,'JKA' ,estimate_percent=>100 ,cascade=>TRUE); END;
This query can use a range scan on jka_normal:
SELECT * FROM jka WHERE v LIKE 'ABC%';
But this query will use a full table scan (can’t use the index):
SELECT * FROM jka WHERE v LIKE '%ABC';
Now, create a function-based index (not to be confused with a REVERSE INDEX):
CREATE INDEX jka_reverse ON jka(REVERSE(v)); BEGIN dbms_stats.gather_table_stats (USER ,'JKA' ,estimate_percent=>100 ,cascade=>TRUE); END;
This query can use a range scan on jka_reverse:
SELECT * FROM jka WHERE REVERSE(v) LIKE REVERSE('%ABC');
Edit: looks like Richard Foote beat me to it, in a discussion of reverse indexes (which unfortunately don’t contribute anything useful to this particular problem) – I did a quick search on Google without results, but Foote’s article must not have been indexed yet or too far down the list.