RE: strategies for optimizing 'like' operations
- From: "Stephens, Chris" <chris_stephens@xxxxxxxxxxxx>
- To: <rjoralist@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 7 Mar 2008 12:40:30 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rich Jesse
Sent: Friday, March 07, 2008 12:31 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
