Re: Taking advantage of reverse index?

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<Dmxz8.32659$vX.3562_at_rwcrnsc53>...
> 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
>

Yep...I understand the purpose of it. But as a side effect, if Oracle were to choose to take advantage of it, it could be used to greatly speed up performance of searches where there is a wildcard at the beginning. Apparently, they didn't choose to take advantage of that...

>
> "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 - 20:08:30 CEST