Re: strategies for optimizing 'like' operations
- From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- To: chris_stephens@xxxxxxxxxxxx
- 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@xxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
