Re: Taking advantage of reverse index?
Date: 1 May 2002 05:55:53 -0700
Message-ID: <7e388bc3.0205010455.55bd6aac_at_posting.google.com>
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