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. =20
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. =20

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

------------------------------------------------------------------------
----
----------------------------------------
create table MonTransRateInc (

          Sellt                         Date,
          RateInc                       NUMBER(11),         =20
          Counter                       NUMBER(11),           =20
          Facility                      NUMBER(6),            =20
          GROSS_REV                     NUMBER(11),
          Validations                   NUMBER(11),
          constraint
                prim_MonTransRateInc primary key
(Sellt,RateInc,Facility)
                        using index
                        storage ( initial 5M next 5M MAXEXTENTS 121
PCTINCREASE 50 )
                        tablespace report) storage ( initial 5M next 5M
MAXEXTENTS 121 PCTINCREASE 10 )
                        tablespace report;

------------------------------------------------------------------------
----
---------------------------------
create or replace procedure proc_MonTransRateInc (EndDate DATE,
maxlTotal
number,Net boolean) IS

        Fetched BOOLEAN :=3D False;

        CURSOR cur_move (EndDate DATE, maxlTotal number) IS
=09
                SELECT  tmov.class,
                        tmov.artid,
                        tmov.ZIDLOCAL,
                        tmov.ZIDDEVICE,
                        tmov.vouch,
                        tmov.TOTAL,
                        tmov.TOTQUA,
                        trunc(tmov.sellt) SELLT,
                        tmov.Attrib,
                        tmov.Facility
                FROM shadow.tmovement tmov, shadow.tShift TS
                WHERE   trunc(ts.tEndTime) between=20
=09
to_date('01'||to_char(EndDate,'mmyyyy'),'ddmmyyyy')
                                AND LAST_DAY(trunc(EndDate))
                and      trunc(tmov.Sellt) between=20
=09
to_date('01'||to_char(EndDate,'mmyyyy'),'ddmmyyyy')
                                AND LAST_DAY(trunc(EndDate))
                        AND mod(tmov.devid,65536) between 600 and 899
                        and ts.ZIDSHIFTLOCAL=3Dtmov.ZIDLOCAL
                        and ts.ZIDSHIFTDEVICE=3Dtmov.ZIDDEVICE
                        and bshiftState =3D 4
                        and trunc(sellt)=3Dto_date('01122003','ddmmyyyy')
                        --and Total <=3D maxlTotal=20
                ORDER BY tmov.zidlocal, tmov.ziddevice, tmov.vouch,
tmov.Sellt, tmov.class, tmov.artid;

        rec_move                cur_move%ROWTYPE;
        rec_oldmove             cur_move%ROWTYPE;
        rec_sum         db.MonTransRateInc%ROWTYPE;

BEGIN
        Delete from db.MonTransRateInc;
        OPEN cur_move(EndDate, maxlTotal);

        rec_sum.Sellt           :=3Dto_date('01011977','ddmmyyyy');
        rec_sum.RateInc         :=3D0;
        rec_sum.Facility        :=3D0;
        rec_sum.GROSS_REV       :=3D0;
        rec_sum.Validations     :=3D0;

        FETCH cur_move INTO rec_move;
        rec_oldMove:=3Drec_move;
        LOOP
                IF  (rec_move.ZIDLOCAL <> rec_oldMove.ZIDLOCAL OR
                     rec_move.ZIDDEVICE <> rec_oldMove.ZIDDEVICE OR
                     rec_move.vouch <> rec_oldMove.vouch)
                     OR (cur_move%NOTFOUND AND Fetched=3DTrue) THEN

                        --dbms_output.put_line('U');
=09
--dbms_output.put_line('Count=3D'||to_char(rec_sum.Count));
=09
--dbms_output.put_line('Gross_rev=3D'||to_char(rec_sum.Gross_rev));
=09
--dbms_output.put_line('Sellt=3D'||to_char(Rec_Sum.Sellt,'ddmmyyyy
hh24miss'));
=09
--dbms_output.put_line('RateInc=3D'||to_char(Rec_sum.RateInc));
=09
--dbms_output.put_line('Facility=3D'||to_char(rec_sum.Facility));
                        Fetched:=3DFalse;
                        UpDate db.MonTransRateInc
                                Set Counter
=3DCounter+rec_sum.Counter,
                                Gross_rev
=3DGross_Rev+rec_sum.Gross_rev,
                                Validations
=3DValiDations+rec_sum.validations
                        where   Facility=3Drec_sum.Facility
                        and     trunc(Sellt)=3Dtrunc(Rec_Sum.Sellt)
                        and     RateInc=3DRec_sum.RateInc;


                        If SQL%RowCount=3D0 then
                        --dbms_output.put_line('I');

                                INSERT INTO db.MonTransRateInc
                                        (Sellt,=20
                                        RateInc,
                                        Counter,
                                        Facility,
                                        GROSS_REV,
                                        Validations)
                                VALUES
                                        (rec_sum.Sellt,=20
                                        rec_sum.RateInc,
                                        rec_sum.Counter,
                                        rec_sum.Facility,
                                        rec_sum.Gross_rev,
                                        rec_sum.Validations);
                        End If;
                        rec_sum.RateInc:=3D0;
                        rec_sum.Counter:=3D0;
                        rec_sum.Facility:=3D-1;
                        rec_sum.Gross_rev:=3D0;
                        rec_sum.Validations:=3D0;
                        rec_oldMove:=3Drec_move;

                end if;

                EXIT WHEN cur_move%NOTFOUND;

                IF  rec_move.class in (0,1) THEN
                -- Gross Revenue
                        Fetched:=3DTrue;
                        rec_sum.gross_rev :=3D rec_sum.gross_rev +
rec_move.total;
                        rec_sum.Facility        :=3Drec_move.Facility;
                        rec_sum.RateInc :=3Drec_move.Total;
                        rec_sum.Sellt   :=3Drec_move.Sellt;
                        rec_sum.Counter :=3Drec_move.TotQua;
                END IF;

                -- Coupons used=20
                IF  rec_move.class =3D 901 AND rec_move.ARTID !=3D 10 AND=20
                                rec_move.ARTID NOT between 13120 and
13139
THEN
                        Fetched:=3DTrue;
                        rec_sum.Validations     :=3D rec_sum.Validations
+
rec_move.total;
                END IF;

                -- Validation used=20
                IF  rec_move.class =3D 901 AND (rec_move.ARTID between
13120
and 13139)  THEN
                        Fetched:=3DTrue;
                        rec_sum.Validations     :=3D rec_sum.Validations
+
rec_move.total;
                END IF;

                --ISF used
                IF (rec_move.class =3D 901 AND rec_move.ARTID =3D 10 ) THEN
                        Fetched:=3DTrue;
                        rec_sum.Validations  :=3D rec_sum.Validations  +
rec_move.total; =09
                END IF;
                FETCH cur_move INTO rec_move;
                If Net=3DTrue then
                        rec_sum.gross_rev :=3D rec_sum.gross_rev +
rec_sum.Validations;
                        rec_sum.RateInc
:=3Drec_sum.RateInc+rec_sum.Validations;
                        rec_sum.Validations:=3D0;
                End If;
        END LOOP; -- loop
        CLOSE cur_move;
        commit;
END proc_MonTransRateInc;=20
----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: