Re: Taking advantage of reverse index?

"zamo" <ozamorap_at_hotmail.com> wrote in message news:<iAhz8.69587$EK.46047423_at_e3500-atl1.usenetserver.com>...
> When searching within indexed columns, you will be able to take advantage of
> the indexes while making a full match search, or a LIKE search with the
> scope WHERE column like 'search_text%'. If you are searching for
> '%search_text' Oracle wont use the index and it will likely perform a full
> table scan.

<SNIP>

You seem to have missed the point that the index is a REVERSE index. The optimizer cannot use the index to search for 'search_text%' since the data is stored in reverse order. It could conceivably use it for '%search_text', but for some reason will not. I was trying to figure out why.

>
> "Ronnie Schnell" <ronnie_at_twitch.mit.edu> 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 Tue Apr 30 2002 - 08:16:46 CEST