RE: ESCAPE clause causing longer query execution time

From: <traci.l.rebman_at_rrd.com>
Date: Tue, 22 Feb 2005 08:01:34 -0500
Message-ID: <OF52C4EC88.C55F719D-ON86256FB0.00470DD6@rrd.com>

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

---

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.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 22 2005 - 08:04:57 CST