RE: time consuming UPDATE statement
- From: "Justin Cave" <jcave@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2004 01:57:39 -0700
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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
