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

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 175 other followers

%d bloggers like this: