Re: INDEX possible for reverse wildcards?

From: Mikito Harakiri <mikharakiri_at_iahu.com>
Date: Fri, 4 Jun 2004 10:34:05 -0700
Message-ID: <lz2wc.28$924.167_at_news.oracle.com>


"Robert Brown" <robertbrown1971_at_yahoo.com> wrote in message news:240a4d09.0406040759.5e47033f_at_posting.google.com...
> If I use _reverse_ wildcard search will it always result in a table
> scan? Is it possible to get the DB (Oracle or SQL server) to use
> indexes when doing reverse wildcard match?
>
> let's say I have:
>
> table email_address (id int, email varchar)
>
> with the following entries
>
> 2, www.%shoes.%
> 3, w%.super%shoes.%
> 4, %webbox.somecopany.com

Assuming that you have wildcard at the end of the string (which is the only case where normal wildcard index scan can be employed; -- well, with one tiuny exception http://otn.oracle.com/oramag/code/tips2002/100602.html) you table can be reorganized into

table email_address (id int, email_begin_range varchar, email_end_range varchar)

with the entries like this

2, www.shoes., 'www.shoes.'||char(255)
...

> select id from email_address where 'www.superdupershoes.com' like
> email;

And your query becomes

select id from email_address where 'www.shoes.com' between email_begin_range varchar and email_end_range

Finding a set of intervals covering a point is hard. You can employ R-Tree index or Bitmapped index, but it would be never as efficient as B-Tree index range scan in the "reverse" case. Plus, having wildcards in the middle and the of the string would certainly compromise even this idea. Received on Fri Jun 04 2004 - 19:34:05 CEST

Original text of this message