Re: Taking advantage of reverse indexes?

On 29 Apr 2002 20:20:45 GMT, ronnie_at_twitch.mit.edu (Ronnie Schnell) wrote:

>
>I already posted something about this to 'comp.databases.oracle' but
>realized that that newsgroup is not widely distributed...
>
>I have been able to take advantage of indexes for searches using the
>"LIKE" operator in the past. Now I would like to be able to do a quick
>query on domains from e-mail addresses, as in "LIKE '%_at_AOL.COM'", for
>example...as documented, normally this would not take advantage of the
>index (the documentation says that an index can be used as long as the
>leading character is not a wildcard, and that makes perfect sense for
>a normal index). However, if there is a reverse index, I feel like it
>should be able to take advantage of it in this case. It would be just like
>having the wildcard at the end of a normal index. It seems as if he
>optimizer does not realize this. I am using Oracle 9i. Is there any
>way to steer it in the right direction?

You could store a another "redundant" column consisting of just the "right" part of the address, everything after the "@", or you could store a mirror image character-reversed string, i.e. abc_at_def.com -> moc.fed_at_cba Received on Fri May 03 2002 - 22:28:02 CDT