Re: Taking advantage of reverse index?

The indexed is reversed but that does not mean that it will search in reverse. The purpose of the reverse index is to eliminate block contention. For example, if you have an index on an increasing number then new entries will tend to be all in the same block and perhaps cause contention on inserts. If you reverse the index then the least significant bits become the most and the sequential numbers don't look near each other eg 1234,1235,1236 become 4321, 5321, 6321... but the index works for all other intents and purposes just like a real index. You compare on 1234 not 4321. Jim

"Ronnie Schnell" <ronnie_at_twitch.mit.edu> wrote in message news:e17de08c.0204292216.5d4dffac_at_posting.google.com...
> "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 - 15:54:43 CEST