Re: Taking advantage of reverse index?

Seems to me a better solution is to just parse that column into 2 columns , 1 for email id and 1 for domain.

Although you couldn't use this new feature for what you want to do, I have heard a lot of things in 9i that sound pretty good. Have you tried the flashback query , although 9i rel 2 is a LOT better than rel 1 ? What's your opinion of 9i so far?

"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 Thu May 02 2002 - 06:25:41 CEST