Re: strategies for optimizing 'like' operations

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
> like:
>
>
>
> 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-l