Re: Taking advantage of reverse index?

jp_boileau_at_yahoo.com (J.P.) wrote in message news:<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

I think a function index would be better on either of those columns, according to my testing...

>
> 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 - 20:19:21 CEST