strategies for optimizing 'like' operations

From: Stephens, Chris <>
Date: Fri, 7 Mar 2008 11:03:56 -0600
Message-ID: <>

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?  

The system is  



        This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

Received on Fri Mar 07 2008 - 11:03:56 CST

Original text of this message