XPost: comp.database.oracle, comp.database.oracle.misc, comp.dat
bases.oracle.misc
From: bsc7080xxmqc@myoracleportal.com
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 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@myoracleportal.com
(remove XX to contact)
****************************************************************
Resource Library is now Online @ http://www.MyOraclePortal.com
****************************************************************
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
