Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: INDEX possible for reverse wildcards?

Re: INDEX possible for reverse wildcards?

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Fri, 04 Jun 2004 16:07:58 GMT
Message-ID: <y71wc.1496$uX2.730@newsread2.news.pas.earthlink.net>


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.

Basically, the only way I can answer the question mentally is to look at each wildcard pattern and see whether the 'www.superdupershoes.com' string matches it - and that's what the optimizers are doing too. So, the whole table scan is necessary.

> 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
>
> select id from email_address where 'www.superdupershoes.com' like
> email;
>
> this returns 2,3
>
> But the query always results in a table scan even if I add an index to
> email. What kind of index can I employ in this situation?

None that I can think of.

> Please note that this is a _reverse_ search, the opposite of what's
> normally done, i.e. select from email_address where email like
> 'www.%shoes.com'.

The example was a good idea - it explains clearly what you're after.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Received on Fri Jun 04 2004 - 11:07:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US