RE: ESCAPE clause causing longer query execution time
- From: traci.l.rebman@xxxxxxx
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 22 Feb 2005 08:01:34 -0500
Thanks for the reponses...I have attached the query. Against our
production database this query takes over 2 and a half minutes to run,
that same query without the underscore (EZCOMPARE) takes only a few
seconds.
select count(*)
from pf_document_rv
where ((r_folder_path like '/Jobs/99502/Assembly/EZ~_COMPARE/%' escape
'~')
and (lower(r_object_id) <> lower('f0053000000ab46b'))) and r_lock_owner
IS NOT NULL
Thanks
Traci
Wolfson Larry - lwolfs <Larry.Wolfson@xxxxxxxxxx>
02/21/2005 01:12 PM
To: traci.l.rebman@xxxxxxx, Oracle-L@xxxxxxxxxxxxx
cc:
Subject: RE: ESCAPE clause causing longer query execution time
Like the other guys said I'd like to see the whole code.
I thought that the index only used EZ in your first query and then checked
everything while in your second the index returned an exact match.
Larry
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of traci.l.rebman@xxxxxxx
Sent: Friday, February 18, 2005 1:41 PM
To: Oracle-L@xxxxxxxxxxxxx
Subject: ESCAPE clause causing longer query execution time
Hello,
I have a query using LIKE with the ESCAPE clause to search for an
underscore '_' as a literal value. When I run the query with the ESCAPE
clause it takes much longer then when the same query is run without the
ESCAPE clause. I thought maybe it was an index issue, but explain plans
for both queries are identical. I also tried rebuilding the indexes on
the table, but that made no improvement. Does anyone have any
suggestions...I am stumped!
Time to delete row with value "EZ_COMPARE" ==> 2 min. 54 secs.
Time to delete row with value "EZCOMPARE" ==> 2 secs.
Thank you in advance,
Traci L. Rebman
--
//www.freelists.org/webpage/oracle-l
**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not 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 re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
