| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: backwards LIKE matching
A trailing wildcard will use an index, however the inverse would not (leading wildcard). A way to do the inverse would be to create
a reverse function index on the column and place the wildcard at the end still. In effect you get the wildcard on the lead then, but
just through a different means.
On Thu, 07 Sep 2006 05:00:22 -0400, Ariel Shkedi <as_at_dsgml.com> wrote:
>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
BChase
bsc7080mqcXX_at_myoracleportal.com
(remove XX to contact)
 |
 |