RE: strategies for optimizing 'like' operations

The problem with oracle text as I understand it is that the index must be manually maintained.

Our users have an expectation of immediate availability through the search.

Thanks for the suggestion!

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Friday, March 07, 2008 12:31 PM
To: oracle-l_at_freelists.org
Subject: Re: strategies for optimizing 'like' operations

Hey Chris,

> 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 10.2.0.3
>

You may want to research Oracle Text (nee ConText) which has the ability to
index bits and pieces of strings and other objects. This has the effect of
tokenizing or creating tags of the indexed items, which can then be searched
more efficiently. I implemented this in a past life in 9.2.0 with decent
results. A few items to note:

1. Licensing? While I'm almost positive that it's included w/Enterprise, you'll want to make sure.
2. It may not be installed/enabled in your database. Metalink article 275689.1 discusses this for 9iR2, but I'm not sure about 10gR2.
3. Be prepared not to have much support from SRs. When I had issues configuring stop lists for Oracle Text (e.g. to tell Text that "1.234" is a single entity and not two entities divided by a period, which was the default for my installation), I had very little assistance from Oracle Support. YMMV!
4. Consider architecting the app to do the tokenizing yourself -- it's probably as much work up front, but not implementing a less-used option in Oracle usually means less maintenance and headaches!
5. Beware that the SELECT statement must use the CONTAINS clause within the WHERE clause in order to use the Text index(es) -- standard WHERE clauses will not work.
6. Metalink 268001.1 is the library doc for Oracle Text and may be a good place for more information.

HTH! GL! Rich

--
http://www.freelists.org/webpage/oracle-l



CONFIDENTIALITY NOTICE: 
	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.
--
http://www.freelists.org/webpage/oracle-l