Re: INDEX possible for reverse wildcards?

Jonathan Leffler <jleffler_at_earthlink.net> wrote:
> Robert Brown wrote:
>
> > If I use _reverse_ wildcard search will it always result in a table
> > scan?
>
> I think so. How else do you do it? You can't generate a list of all
> possible wildcards that the string might match, or anything like that.

Well, you would only need to look at index entries that start with 'w', '%', or '_'. And if the first character is a 'w' or a '_', you only need to look at ones where the second is 'w', '%', or '_'. And so on. Kind of like a skip scan. (or course, in the example given, this wouldn't gain you anything, as they all either start with 'w' or a wild-card.)

Note that I am not saying that Oracle actually does do this, I'm simply saying that it would be conceptually possible for it to do so, without requiring any changes to the index structure.

> > table email_address (id int, email varchar)
> >
> > with the following entries
> >
> > 2, www.%shoes.%
> > 3, w%.super%shoes.%
> > 4, %webbox.somecopany.com
> >
> > select id from email_address where 'www.superdupershoes.com' like
> > email;

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB