LIKE with wildcard (%) at start CAN use an index
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.
– create some test data
CREATE TABLE jka AS
SELECT ROWNUM id, dbms_random.string(‘x’,10) v
FROM dual CONNECT BY LEVEL <= 10000;
– create an ordinary index
CREATE INDEX jka_normal
ON jka (v);
– range scan on jka_normal
SELECT * FROM jka WHERE v LIKE ‘ABC%';
– full table scan
– (can’t use the index)
SELECT * FROM jka WHERE v LIKE ‘%ABC';
– create a function-based index
– (not to be confused with a REVERSE INDEX)
CREATE INDEX jka_reverse
– 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.