In Part I, we saw how with Reverse Key Indexes, Oracle will basically take the indexed value, reverse it and then index the reversed value. As a result, data that would ordinarily be logically sorted within an index structure will now be randomly distributed. This therefore negates the use of Reverse Key Indexes with range predicates, with the CBO not even considering them in its costings.

This is all the information we need to dispel a rather bizarre suggestion that has been doing the rounds regarding using Reverse Key Indexes to deal with LIKE predicates that have a leading wildcard. For example, such a suggestion can be found here and within an OTN discussion here.

Basically the suggestion is to:

1) Create a Reverse Key Index on the column to be searched with a LIKE predicate having a leading wildcard (such %, _).

2) Instead of writing the query as usual, e.g.

SELECT * FROM bowie_table WHERE name LIKE ‘%BOWIE’

rewrite the query programmatically such as:

SELECT * FROM bowie_table WHERE name LIKE ‘EIWOB%';

by reversing the required text and now having the wildcard at the end.

The Reverse Key Index stores the data in a reversed format identical to say ‘EIWOB’, so Oracle should be able to use the Reverse Key Index to efficiently find all rows that start with ‘EIWOB’ as they’re all grouped together within the index structure, right ?

Ummm, wrong.

Ignoring the fact the example in the above link is somewhat meaningless as it uses a leading and a trailing wildcard in both queries and so assuming the first query only has a leading wildcard and the second query only has a trailing wildcard, this suggested use of a Reverse Key Index can not possibly work on any current version of Oracle.

There are a few fundamental problems with this suggestion but in summary not only will it not work but worse, it will actually return the wrong results.

The suggestion is correct as far as indeed, using a normal index to return data with a LIKE statement containing a leading wildcard will negate the use of an index range scan, the CBO doesn’t even consider it. An index hint may push Oracle to use a Full Index Scan, but not an Index Range Scan.

However using a Reverse Index Key to solve this is unfortunately doomed to failure for two very simple reasons.

One, as we have already seen, Oracle also ignores Index Range Scans for Reverse Key Indexes with range predicates and unfortunately, a query such as WHERE name LIKE ‘EIWOB%’ is a range scan. The CBO simply doesn’t consider the Reverse Key Index in it’s deliberations.

Two, is of course that Oracle has no possible way of knowing that when you say LIKE ‘EIWOB%’, what you really mean is search for all records ending with BOWIE, LIKE ‘%BOWIE’. How can Oracle possibly know this ? If it could use the index (which it can’t) Oracle would only reverse the search string around anyways and use the index to look for indexed entries beginning with ‘BOWIE’ within the index structure, remembering everything is of course stored in reverse within the index.

So Oracle is actually searching for all records starting with ‘EIWOB’, not ending with ‘BOWIE’ which are two entirely different things.

The net result of using this suggested strategy is not good.

1) Oracle ignores the Reverse Key Index anyways as a LIKE ‘EIWOB%’ is a range predicate
2) Oracle therefore performs a Full Table Scan anyways
3) As the query is effectively searching for all records that start with ‘EIWOB’, not as expected all records that end with ‘BOWIE’, the two queries in the example will actually return completely different results

However, if you want to solve the issue of efficiently finding the results of a LIKE ‘%BOWIE’, there are some possible approaches one could take that will use an index and return correct results.

One possible solution (as mentioned in the OTN link listed at the beginning) is to create a Function-Based Index using the REVERSE Function, (Warning: this function is undocumented and unsupported):

CREATE INDEX bowie_reverse_func_i ON bowie(REVERSE(name));

A query such as WHERE REVERSE(name) LIKE ‘EIWOB%’ or better still WHERE REVERSE(name) LIKE REVERSE(‘%BOWIE’) can now both potentially use the index.

The reverse function will reverse the name column (from say ‘DAVID BOWIE’ to ‘EIWOB DIVAD’) and the LIKE range predicate can work with the index as it’s a Function-Based index rather than a Reverse Key Index and it’s not using a LIKE with a leading wildcard. A column containing ‘DAVID BOWIE’, but stored as ‘EIWOB DIVAD’ within the index, can be found efficiently via an index range scan using this Function-Based Index.

I’ve included an example on effectively using a Function-Based Index with the Reverse Function at the end of the above demo. There’s also a discussion and other alternatives at Gints Plivna’s Blog.

Another alternative is to use an Oracle Text Index, which also has the capability of dealing logically with queries such as %BOWIE% but as they say, that’s a topic for another day.

so now you’re also sneaking in references to Queen. :-) All artists from the UK – coincidence?

I believe there is a touch too much “not” in “Warning: this function is not undocumented and unsupported” otherwise I don’t understand the point of the warning.

I am also missing the case where you reverse the index but use the original query

SELECT * FROM reverse_stuff WHERE object_name LIKE ‘%BOWIE';

Theoretically, since the column is reversed in the index, Oracle could do a range scan with this because it can use the leading portion of the reversed column much the same as with LIKE ‘DAVID%’ when there is a non reversed index.

Thanks Richard, you stole my thunder. Just this morning I was considering the plight of a customer whose ‘%MITH’ queries weren’t running fast enough for them. A quick search on google didn’t come up with anything useful, unfortunately. I hadn’t caught up on my blog reading either.

My first thought was that maybe, just maybe, the new REVERSE indexes might help, thinking that the optimiser might be smart enough to convert WHERE name LIKE ‘%MITH’ into something like WHERE internal_reverse_function(name) LIKE ‘HTIM%’ (assuming that name only contains single-byte strings, and internal_reverse_function returns the input byte-reversed exactly like the reverse index stores it, and the function is specially recognised by the optimiser).

So of course a reasonable answer is as you say, to create a function-based index on the reverse function; but unfortunately to use it I have to modify the application code, which in some cases is not a cost effective option.

Oracle’s new reverse indexes were never intended for this purpose anyway, I’ve always heard them explained in terms of reducing insert contention for monotonically increasing values like meaningless IDs.

My big concern with the reverse function is that it’s not supported, probably because of the issues due to multi-byte characters. But yes, it also requires the application to be written to reference the function.

I found out why reverse is not supported:-
1* select reverse (123456) from dual
p01cfd> /
Segmentation fault (core dumped)
-bash-3.00$
If anyone asks, I was not logged into a production system when I did this.
I feel so Low.

Indeed. It’s also why you run into issues multi-byte character sets. The reverse function is used by Oracle internally for specific operations and is not meant to be used by those with too imagination and time on their hands ;)

If insert performance is not problematic, then a good old non-reverse index will of course be fine as well.

Other options could be to use a hash cluster for the table (and so potentially avoid a problematic index), use hash partitioning on the table and/or index to spread inserts across different partitions.

Hi, Thanks for the reply. It is fantastic :-) As you suggested in your earliar reply, you said: Other options could be to use a hash cluster for the table (and so potentially avoid a problematic index), use hash partitioning on the table and/or index to spread inserts across different partitions. If the table exists already, do I need to recreate it into a cluster table? Can a cluster index be created only a clustred table, or it can be created on a non-clustered table as well? I sort of know the answer but not sure. Thanks and reagdrs