Re: Taking advantage of reverse index?

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.

What u might do is create a function-based index on the field to search for the most common searchs you do.
Eg. create index ndame_index_idx on table (INSTR(email,'_at_aol.com',1,1))

so when you search for aol adreesses you can query select email from table

    where INSTR(email,'_at_aol.com',1,1) > 0

In that case oracle will use the index. And it will perform a range scan.

I will consider adding function-based indexes only to tables that are huge and you query them many times a day.

PS: you need these in your init.ora
query_rewrite_integrity=trusted
query_rewrite_enabled=true

"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 Mon Apr 29 2002 - 22:08:22 CEST