Re: strategies for optimizing 'like' operations
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 7 Mar 2008 12:30:52 -0600 (CST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
