Re: time consuming UPDATE statement
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2004 08:54:26 -0000
Your cursor truncates the input date:
trunc(tmov.sellt) SELLT,
Your only (PK) index is
(Sellt,RateInc,Facility)
Your update statement uses a predicate
that makes the index unavailable to the optimizer
and trunc(Sellt)=trunc(Rec_Sum.Sellt)
So my first guess would be that you are building
a table and taking more and more time to update
it because of the ever-increasing tablescan.
It looks as if you could get rid of the trunc()
on both sides of the predicate in the update,
although the only one you have to get rid of
is the one on the left hand side.
In passing - you don't have a constraint on
the sellt to force it to be date-only, so in
principal your update could update more
than one row. (Although in the context of
this extract supplied, this can't happen).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
Jan 29th 2004 UKOUG Unix SIG - v$ and x$
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Foelz.Frank" <Foelz.Frank@xxxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, January 30, 2004 8:37 AM
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),
Counter NUMBER(11),
Facility NUMBER(6),
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 := False;
CURSOR cur_move (EndDate DATE, maxlTotal number) IS
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
to_date('01'||to_char(EndDate,'mmyyyy'),'ddmmyyyy')
AND LAST_DAY(trunc(EndDate))
and trunc(tmov.Sellt) between
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=tmov.ZIDLOCAL
and ts.ZIDSHIFTDEVICE=tmov.ZIDDEVICE
and bshiftState = 4
and trunc(sellt)=to_date('01122003','ddmmyyyy')
--and Total <= maxlTotal
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 :=to_date('01011977','ddmmyyyy');
rec_sum.RateInc :=0;
rec_sum.Facility :=0;
rec_sum.GROSS_REV :=0;
rec_sum.Validations :=0;
FETCH cur_move INTO rec_move;
rec_oldMove:=rec_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=True) THEN
--dbms_output.put_line('U');
--dbms_output.put_line('Count='||to_char(rec_sum.Count));
--dbms_output.put_line('Gross_rev='||to_char(rec_sum.Gross_rev));
--dbms_output.put_line('Sellt='||to_char(Rec_Sum.Sellt,'ddmmyyyy
hh24miss'));
--dbms_output.put_line('RateInc='||to_char(Rec_sum.RateInc));
--dbms_output.put_line('Facility='||to_char(rec_sum.Facility));
Fetched:=False;
UpDate db.MonTransRateInc
Set Counter =Counter+rec_sum.Counter,
Gross_rev
=Gross_Rev+rec_sum.Gross_rev,
Validations
=ValiDations+rec_sum.validations
where Facility=rec_sum.Facility
and trunc(Sellt)=trunc(Rec_Sum.Sellt)
and RateInc=Rec_sum.RateInc;
If SQL%RowCount=0 then
--dbms_output.put_line('I');
INSERT INTO db.MonTransRateInc
(Sellt,
RateInc,
Counter,
Facility,
GROSS_REV,
Validations)
VALUES
(rec_sum.Sellt,
rec_sum.RateInc,
rec_sum.Counter,
rec_sum.Facility,
rec_sum.Gross_rev,
rec_sum.Validations);
End If;
rec_sum.RateInc:=0;
rec_sum.Counter:=0;
rec_sum.Facility:=-1;
rec_sum.Gross_rev:=0;
rec_sum.Validations:=0;
rec_oldMove:=rec_move;
end if;
EXIT WHEN cur_move%NOTFOUND;
IF rec_move.class in (0,1) THEN
-- Gross Revenue
Fetched:=True;
rec_sum.gross_rev := rec_sum.gross_rev +
rec_move.total;
rec_sum.Facility :=rec_move.Facility;
rec_sum.RateInc :=rec_move.Total;
rec_sum.Sellt :=rec_move.Sellt;
rec_sum.Counter :=rec_move.TotQua;
END IF;
-- Coupons used
IF rec_move.class = 901 AND rec_move.ARTID != 10 AND
rec_move.ARTID NOT between 13120 and 13139
THEN
Fetched:=True;
rec_sum.Validations := rec_sum.Validations +
rec_move.total;
END IF;
-- Validation used
IF rec_move.class = 901 AND (rec_move.ARTID between 13120
and 13139) THEN
Fetched:=True;
rec_sum.Validations := rec_sum.Validations +
rec_move.total;
END IF;
--ISF used
IF (rec_move.class = 901 AND rec_move.ARTID = 10 ) THEN
Fetched:=True;
rec_sum.Validations := rec_sum.Validations +
rec_move.total;
END IF;
FETCH cur_move INTO rec_move;
If Net=True then
rec_sum.gross_rev := rec_sum.gross_rev +
rec_sum.Validations;
rec_sum.RateInc
:=rec_sum.RateInc+rec_sum.Validations;
rec_sum.Validations:=0;
End If;
END LOOP; -- loop
CLOSE cur_move;
commit;
END proc_MonTransRateInc;
----------------------------------------------------------------
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:
