| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: time consuming UPDATE statement
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
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
Sellt Date,
RateInc NUMBER(11),
Counter NUMBER(11),
Facility NUMBER(6),
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,
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);
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;
rec_sum.gross_rev := rec_sum.gross_rev + rec_sum.Validations; rec_sum.RateInc
 |
 |