Re: strategies for optimizing 'like' operations

  • From: "Bradd Piontek" <piontekdd@xxxxxxxxx>
  • To: chris_stephens@xxxxxxxxxxxx
  • Date: Fri, 7 Mar 2008 12:51:52 -0600

I'm not sure if these will work, but worth a shot:
1. Combination of a function-based index on UPPER(keyword) (if it doesn't
already exist )  and using SYS_CONTEXT() to make the search look like a bind
variable and possibly use the index. If seen like '%blah%' not use indexes
but like 'blah%' will (selectivity?).

2. Not sure if the REGEXP could help in this case.  WHERE
REGEXP(UPPER(keyword),'regfexphere'). I haven't played around with how the
optimizer treats this.

Other related posts: