| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ESCAPE clause causing longer query execution time
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?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
traci.l.rebman_at_rrd.com
Sent: Tuesday, February 22, 2005 8:02 AM
To: Oracle-L_at_freelists.org
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_at_acxiom.com> 02/21/2005 01:12 PM
To: traci.l.rebman_at_rrd.com, Oracle-L_at_freelists.org
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of traci.l.rebman_at_rrd.com
Sent: Friday, February 18, 2005 1:41 PM
To: Oracle-L_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 22 2005 - 09:16:05 CST
 |
 |