AW: time consuming UPDATE statement

From: "Foelz.Frank" <Foelz.Frank@xxxxxxxxxxxxxxxxxxx>
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 30 Jan 2004 10:34:06 +0100

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@xxxxxxxxxxx]
Gesendet am: Freitag, 30. Januar 2004 09:58
An: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Foelz.Frank
Sent: Friday, January 30, 2004 1:38 AM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

AW: time consuming UPDATE statement

Other related posts: