Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: time consuming UPDATE statement

RE: time consuming UPDATE statement

From: Justin Cave <jcave_at_ddbcinc.com>
Date: Fri, 30 Jan 2004 01:57:39 -0700
Message-ID: <87E9F113CEF1D211A4C3009027301874195BF6@ddbcinc.ddbc.local> 





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


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Foelz.Frank
Sent: Friday, January 30, 2004 1:38 AM


To: 'oracle-l_at_freelists.org'


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;






		--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_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
-----------------------------------------------------------------


----------------------------------------------------------------
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:57:39 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US