RE: strategies for optimizing 'like' operations
- From: "Stephens, Chris" <chris_stephens@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Mar 2008 12:40:17 -0500
The developer actually discovered that a piece of the sql was wrong.
Once corrected and using the rownum trick, of which I was not aware,
performance is more than adequate.
We are trying to achieve a search that runs across attributes of a book
and it's pages. The query returns both the book id and page id.
Thank you for everyone's help!
-----Original Message-----
From: Bob Carlin [mailto:smeghead.rimmer@xxxxxxxxx]
Sent: Saturday, March 08, 2008 10:34 AM
To: Stephens, Chris
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: strategies for optimizing 'like' operations
Chris,
Can you explain what you are actually trying to achieve? You can see
from some of the replies that some inferences and assumptions ate being
made, and so the answer your are really looking for will depend on what
you are really trying to do.
Stephens, Chris wrote:
>
> 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
>
> Thanks,
>
> chris
>
>
> 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.
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:
