Bulk Update ...

[Sameer]

Hi,

Table has 1 million + recs. Following proc takes first 3 distict names from the table and updates those 3 names
to the whole table. You may change 3 to any number. Proc update records in batches (in example, it makes 3
batches) within the FOR LOOP, and takes different names. In your case you need first 1000.

suggestion: alter table to nologging (to spead up), analyze table before running proc.

SQL> DESC tbl_ITB
 Name           Null?    Type
 -------------- -------- ----------------------------
 SCHLUESSEL     NOT NULL NUMBER       -- PK
 VORNAME                 VARCHAR2(40)
 NAME                    VARCHAR2(40)

SQL> SELECT name, vorname, COUNT (1) 
  2  FROM tbl_ITB                        
  3  GROUP BY name, vorname ORDER BY 3 ;

NAME                       VORNAME         COUNT(1)
-------------------------- ------------- ----------
Batt                       Kirsten              76834
Ulmann                     Sabine              102432
Forester                   Jamal               128032
Nash                       Deter               368430
Bremer                     Barco               433230
Solter                     Tarkus              607434

6 rows selected.

Elapsed: 00:00:00.03

--  Table has 3 distinct names, we will update complete table using proc with first 3 names.

SQL> EXEC proc_anonym ;

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.96
SQL> SELECT name , vorname , COUNT ( 1 ) 
  2  FROM tbl_ITB                        
  3  GROUP BY name , vorname ORDER BY 3 ;

NAME             VORNAME         COUNT(1)
---------------- ------------- ----------
Forester         Jamal               572130
Batt             Kirsten             572131
Ulmann           Sabine              572131

Elapsed: 00:00:00.02

All records got updated with first 3 names.
------------------------------------------------------------

CREATE OR REPLACE procedure proc_anonym AS 

TYPE ty_schluessel IS TABLE OF tbl_ITB.schluessel %TYPE; 
TYPE ty_vorname IS TABLE OF tbl_ITB.vorname %TYPE; 
TYPE ty_name IS TABLE OF tbl_ITB.name %TYPE; 
TYPE ty_cName IS REF CURSOR; 

t_schluessel ty_schluessel; 
t_vorname ty_vorname; 
t_name ty_name; 
cName ty_cName ; 

sVorname tbl_ITB.vorname %TYPE; 
sname tbl_ITB.vorname %TYPE; 
nBatch NUMBER ; 
nStart NUMBER ; 
nEnd NUMBER ; 


BEGIN

	SELECT ROUND (MAX(schluessel)/ 3) 
	INTO nBatch 
	FROM tbl_ITB ; 
	
	nStart := 0 ; 
	nEnd := nBatch ; 
	
	OPEN cName FOR SELECT vorname, name 
	    FROM (SELECT name, vorname, COUNT(1) 
	    FROM tbl_ITB 
	    GROUP BY name, vorname ORDER BY 3 ) 
	    WHERE ROWNUM <= 3 ; 
	
	LOOP     
	FETCH cName INTO sVorname , sName ; 
	EXIT WHEN cName %NOTFOUND; 
		--DBMS_OUTPUT.PUT_LINE('Start '||TO_CHAR(nStart) || ' End ' ||TO_CHAR(nEnd)); 
	
	    SELECT rownum, vorname , name 
	    BULK COLLECT INTO t_schluessel , t_vorname , t_name 
	    FROM tbl_ITB 
	    WHERE schluessel BETWEEN nStart AND nEnd ; 
	
	    FOR i IN 1 .. t_schluessel.COUNT 
		   LOOP 
	          UPDATE tbl_ITB 
	          SET vorname = sVorname , 
	              name = sName 
	          WHERE schluessel = t_schluessel (i) + nStart; 
	       END LOOP;
	 
	       COMMIT; 
	 
	    nStart := nEnd ; 
	    nEnd := nEnd + nBatch ; 
	
	END LOOP; 
	CLOSE cName ;
EXCEPTION WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE (SQLERRM);     
END;
/

HTH

Sameer