Re: strategies for optimizing 'like' operations
Date: Fri, 7 Mar 2008 11:35:17 -0800
Can you create functional indexes on 'upper(a.keyword)' and 'upper(b.keyword)'? Better yet, store keyword as upper, just use a regular index.
A single like with a leading wild card equates to a 5% selectivity which should be small enough to give an index access plan.
On 3/7/08, Stephens, Chris <chris_stephens_at_admworld.com> wrote:
> A developer is trying to implement some search functionality in an apex
> application that searches across several different text columns in several
> different tables. The query is taking entirely too long. The sql looks
> WHERE upper(a.keyword) like '%' || upper(:B1) || '%' or
> Upper(b.keyword) like '%' || upper(:B2) ||'%'
> Is there any way to enable index access with a query like this? Are there
> design strategies that can make this type of thing performant?
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 07 2008 - 13:35:17 CST