Re: Taking advantage of reverse index?

From: J.P. <jp_boileau_at_yahoo.com>
Date: 1 May 2002 05:55:53 -0700
Message-ID: <7e388bc3.0205010455.55bd6aac_at_posting.google.com>


Y'all are making this sooooo complicated for nothing.

Add a column to your table, called REVERSE_COLUMN Create a trigger that fills the REVERSE_COLUMN automatically Create an index on REVERSE_COLUMN
Make sure that the search is performed with the backwards of the search key.

I've done it. It works fine.

It's pretty similar to having a column containing the UPPERCASE of anoter column.

JP

ronnie_at_twitch.mit.edu (Ronnie Schnell) wrote in message news:<e17de08c.0204291110.72afbc36_at_posting.google.com>...
> I'm using Oracle 9i. I've had success using indexes to speed up
> searches using 'LIKE' in the past. Now I want to perform searches on
> things like domain names within an e-mail address...like, say I wanted
> to return anyone_at_aol.com. I thought it would be cool to use a
> "reverse index" and hope the optimizer would figure out that if I do a
> query "LIKE '%_at_AOL.COM'" it can take advantage of the reverse index to
> do this. It seems like it does not. Once I make the index REVERSE,
> it will always use a "FAST FULL" index scan, no matter where I put the
> '%'. If the index is NOREVERSE, it will do a fast scan of the index,
> so long as the percent is not at the beginning (as documented). It is
> a shame if there is no way to make it do the reverse (as long as the %
> isn't at the end) for a reverse index. Is there a way to steer it in
> that direction?
Received on Wed May 01 2002 - 14:55:53 CEST

Original text of this message