AW: time consuming UPDATE statement

From: Foelz.Frank <Foelz.Frank_at_Scheidt-Bachmann.de>
Date: Fri, 30 Jan 2004 10:34:06 +0100
Message-ID: <184C07CA956B59418F925AACD4DF4CDE71B7F6@mail-server.iiv.scheidt-bachmann.de>

Justin, (and Jonathan)

I removed the Trunc on both sides. I realized a run time of 19 mins = now.
Still heavy but faster :-)

Referring to your sugg. with the SQL version...... I am not quite sure = how
to do that, as the sums that
are built, are out of up to 9 (or less) rows of data within the = cur_move
cursor.....

but I will think a bit more on that

anyway, TNX guy's

Frank

-----Urspr=FCngliche Nachricht-----

Von: Justin Cave [mailto:jcave_at_ddbcinc.com] Gesendet am: Freitag, 30. Januar 2004 09:58 An: oracle-l_at_freelists.org
Betreff: RE: time consuming UPDATE statement

My hunch here would be that there are two problems:

1. You're applying a function to one of the columns in the WHERE = clause, trunc(Sellt), which will force Oracle to do a full table scan to identify the row to be updated. =3D20
2. You are doing in a PL/SQL cursor loop what ought to be done in SQL. If you were to recode this as a few SQL statements, rather than as a cursor loop, you would vastly reduce the number of times that MonTransRateInc needs to be scanned. In the PL/SQL version, there will be one full table scan for every row in the cur_move cursor-- if you rewrite it as a single SQL statement, you'll only need to full scan the table once. =3D20

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Foelz.Frank Sent: Friday, January 30, 2004 1:38 AM
To: 'oracle-l_at_freelists.org'
Subject: time consuming UPDATE statement

Hi Gurus,

sorry for the long posting, but I am kinda lost.........

In the below procedure, there is an Update Stement. This is so timeconsuming, that the whole procedure takes 1 1/2 hrs to complete. If the
UPDATE is removed, the whole thing is done in 3-4 mins. So there is nothing
else eating up the time, but the Update....

BUT WHY ????????? I am on Oracle 7.3.4

Any hints are very welcome

Frank

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Fri Jan 30 2004 - 03:34:06 CST