Re: strategies for optimizing 'like' operations

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Fri, 7 Mar 2008 12:30:52 -0600 (CST)
Message-ID: <23583.12.17.117.251.1204914652.squirrel@12.17.117.251>


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
Received on Fri Mar 07 2008 - 12:30:52 CST

Original text of this message