Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Taking advantage of reverse indexes?
You might look at creating a function based index to create an index
on the portion of the string you want indexed.
-- Pablo Sanchez, High-Performance Database Engineering mailto:pablo_at_hpdbe.com Available for short-term and long-term contracts "Ronnie Schnell" <ronnie_at_twitch.mit.edu> wrote in message news:3ccdab1d$0$3932$b45e6eb0_at_senator-bedfellow.mit.edu...Received on Mon Apr 29 2002 - 17:11:27 CDT
>
> I already posted something about this to 'comp.databases.oracle' but
> realized that that newsgroup is not widely distributed...
>
> I have been able to take advantage of indexes for searches using the
> "LIKE" operator in the past. Now I would like to be able to do a
quick
> query on domains from e-mail addresses, as in "LIKE '%_at_AOL.COM'",
for
> example...as documented, normally this would not take advantage of
the
> index (the documentation says that an index can be used as long as
the
> leading character is not a wildcard, and that makes perfect sense
for
> a normal index). However, if there is a reverse index, I feel like
it
> should be able to take advantage of it in this case. It would be
just like
> having the wildcard at the end of a normal index. It seems as if he
> optimizer does not realize this. I am using Oracle 9i. Is there
any
> way to steer it in the right direction?