Re: Taking advantage of reverse index?

You can create a function based index which is the reverse of the string. Then that would help.
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:23:09 CEST