Re: Taking advantage of reverse index?

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Wed, 01 May 2002 05:24:11 GMT
Message-ID: <%ZKz8.84793$CH.12026_at_rwcrnsc52.ops.asp.att.net>


Also if they did that then how would people search for what they want? They would have to reverse things. Defeates the purpose for which they were built. Maybe they should have chosen a better name than reverse index. Jim
"Ronnie Schnell" <ronnie_at_twitch.mit.edu> wrote in message news:e17de08c.0204301008.70f548e1_at_posting.google.com...
> "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 Wed May 01 2002 - 07:24:11 CEST

Original text of this message