time consuming UPDATE statement

From: Foelz.Frank <Foelz.Frank_at_Scheidt-Bachmann.de>
Date: Fri, 30 Jan 2004 09:37:47 +0100
Message-ID: <184C07CA956B59418F925AACD4DF4CDE71B7F5@mail-server.iiv.scheidt-bachmann.de>

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_at_freelists.org 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

---

Received on Fri Jan 30 2004 - 02:37:47 CST