Home -> Usenet -> comp.databases.oracle -> Taking advantage of reverse index?

Taking advantage of reverse index?

From: Ronnie Schnell <ronnie_at_twitch.mit.edu>
Date: 29 Apr 2002 12:10:05 -0700
Message-ID: <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 Mon Apr 29 2002 - 21:10:05 CEST












Original text of this message