backwards LIKE matching - 09-07-2006 , 03:27 AM
I'm reversing the normal order of LIKE, with the column on the right side:
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
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?