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

Home -> Community -> Usenet -> comp.databases.oracle -> Re: backwards LIKE matching

Re: backwards LIKE matching

From: Jim Kennedy <jim>
Date: Thu, 7 Sep 2006 19:44:49 -0700
Message-ID: <csKdnYIeV4DARZ3YnZ2dnUVZ_sCdnZ2d@comcast.com>

"Ariel" <asnews_at_dsgml.com> wrote in message news:S7SdnfVCdbeWfWLZnZ2dnUVZ_oudnZ2d_at_comcast.com...
> I'm reversing the normal order of LIKE, with the column on the right side:
>
> SELECT domain
> FROM tbl_domain
> WHERE 'com.hotmail.subdomain' LIKE domain || '%'
>
> This works, but will this query use an index? If not, is there any way I
> can optimize this? I have flexibility in how I will store the data (as
> you can see I reversed the domain sections for this test).
>
> If I store .hotmail.com (stored here as com.hotmail.) in the database, I
> want to be able to match subdomain.hotmail.com, etc. Or if I store
> .mail.google.com in the DB, I want to match subdomain.mail.google.com
> (but if .mail.google.com is stored in the DB, then .google.com should
> NOT match).
>
> The query as written, technically should be index search able - it just
> needs to look for values starting with c then work through each letter
> of the rest of com.hotmail.subdomain and only look at values starting
> with each prefix, but I don't know if oracle actually does that.
>
> Perhaps a totally different storage method?
>
> -Ariel
>

You could define a function based index that is the substring of the domain. Jim Received on Thu Sep 07 2006 - 21:44:49 CDT

Original text of this message

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