Re: Most efficient large update?

From: broom <broom_at_voicenet.com>
Date: 28 May 2001 07:21:31 -0700
Message-ID: <c948eb61.0105280621.33ac16c2@posting.google.com>

Here's the solution:

---

DECLARE
CURSOR HH_CURSOR IS
SELECT
        rowid,
    FINDER_NUMBER_IND,
    FINDER_NUMBER_HH
FROM

	appl_unq partition (zip9)
	;
  
TYPE ROWLIST 		IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

TYPE INDLIST 		IS TABLE OF appl_unq.FINDER_NUMBER_IND%TYPE 	INDEX BY BINARY_INTEGER;
TYPE HHLIST 		IS TABLE OF appl_unq.FINDER_NUMBER_HH%TYPE 	INDEX BY BINARY_INTEGER;

RID ROWLIST;
INDID INDLIST;
HH HHLIST; ROWS NATURAL := 100000; BEGIN

	OPEN HH_CURSOR;
	LOOP
		FETCH HH_CURSOR BULK COLLECT INTO 
			RID,
			INDID, 
			HH 
			limit rows;
			
		FORALL j IN RID.FIRST..RID.LAST
			UPDATE appl2 SET 
    			FINDER_NUMBER_HH =HH(j)
			WHERE
				FINDER_NUMBER_IND = INDID(j);
		COMMIT;
  
		EXIT WHEN HH_CURSOR%NOTFOUND; 
	END LOOP; 
	
	CLOSE HH_CURSOR;
	
	V_ERR_MSG := SUBSTR(SQLERRM,1,200);
	DBMS_OUTPUT.PUT_LINE('Good Done: ERROR MSG - '|| V_ERR_MSG);

	COMMIT;

EXCEPTION
	WHEN OTHERS THEN

		COMMIT;
  
		V_ERR_MSG := SUBSTR(SQLERRM,1,200);
		DBMS_OUTPUT.PUT_LINE('Bad: ERROR CODE - '|| SQLCODE);
		DBMS_OUTPUT.PUT_LINE('ERROR MSG - '|| V_ERR_MSG);

		COMMIT;
END;                          

/

---

This assumes a partitioned by zip source list. This code will update 4761 records a second on a 3 CPU sun 450.
I'll attempt multiple parallel runs to see if I gain that way. Received on Mon May 28 2001 - 09:21:31 CDT