Skip to content

LIKE with wildcard (%) at start CAN use an index

January 17, 2008

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);

BEGIN
dbms_stats.gather_table_stats
(USER
,’JKA’
,estimate_percent=>100
,cascade=>TRUE);
END;

– 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
ON jka(REVERSE(v));

BEGIN
dbms_stats.gather_table_stats
(USER
,’JKA’
,estimate_percent=>100
,cascade=>TRUE);
END;

– 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.

About these ads

From → SQL

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: