RE: ESCAPE clause causing longer query execution time
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'traci.l.rebman@xxxxxxx'" <traci.l.rebman@xxxxxxx>, Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 22 Feb 2005 09:12:13 -0500
I think displaying the SQL with explain plans for the two versions would be
helpful. What indexes with columns exist on pf_document_rv? Is the data in
r_object_id stored as mixed case, upper?
-- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
traci.l.rebman@xxxxxxx
Sent: Tuesday, February 22, 2005 8:02 AM
To: Oracle-L@xxxxxxxxxxxxx
Subject: RE: ESCAPE clause causing longer query execution time
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
Other related posts:
