XPost: comp.database.oracle, comp.database.oracle.misc, comp.dat   
   bases.oracle.misc   
      
   "Ariel"  wrote in message   
   news:S7SdnfVCdbeWfWLZnZ2dnUVZ_oudnZ2d@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)