Bulk Update ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Bulk Update ...

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    Bulk Update ...

    In Oracle 8.1.6, We trying bulk update on Table with 1 Million records. We wanted to Update names, surnames in a table with Random names, surnames for anonymization of data. So thought of taking first say 1000 names, surnames with highest distinct count and Update all million records with it. So trying following example with count 200. it works. But when I try to modify for whole table it is giving me error. Please help or suggest some other method to anonymize data.

    Thanks & Regards,

    Shailesh

    -------------------------------------------------
    CREATE OR REPLACE PROCEDURE ProTest AS

    TYPE NameTab IS TABLE OF TblTest.NAME%TYPE INDEX BY BINARY_INTEGER;
    TYPE SurNameTab IS TABLE OF TblTest.SURNAME%TYPE INDEX BY BINARY_INTEGER;
    TYPE RidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

    Names NameTab;
    SurNames SurNameTab;
    MyRowId RidTab;

    BEGIN

    SELECT Name BULK COLLECT INTO Names FROM (SELECT Name FROM TblTest
    GROUP BY Name ORDER BY COUNT(Name) DESC) WHERE ROWNUM<=200;

    SELECT SurName BULK COLLECT INTO SurNames FROM (SELECT SURNAME FROM TblTest
    GROUP BY SURNAME ORDER BY COUNT(SURNAME) DESC) WHERE ROWNUM<=200;

    SELECT ROWID BULK COLLECT INTO MyRowId FROM TblTest WHERE ROWNUM<=200;

    FOR rec_Rowid IN MyRowId.FIRST..MyRowId.LAST Loop

    UPDATE TblTest
    SET Name = Names(rec_Rowid),
    SurName = SurNames(rec_Rowid)
    WHERE ROWID = MyRowid(rec_Rowid);

    END LOOP;

    END;
    /
    -------------------------------------------------

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    What is the error..
    Last edited by Sameer; 01-08-2003 at 09:04 AM.

  3. #3
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Hi,

    Please see more description...
    When I try to run following Procedure. Now I have removed ROWNUM while selecting ROWID from table. I get following error.

    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "SYSADM.PROTEST", line 22
    ORA-06512: at line 1


    Ultimately what I want is take 1000 names, surnames with highest distinct count in a table and update same 1000 names, surnames to all records(1 Million)in a table.

    Please Help.

    Thanks & Regards,

    Shailesh
    -----------------------------------------
    CREATE OR REPLACE PROCEDURE ProTest AS

    TYPE NameTab IS TABLE OF TblTest.NAME%TYPE INDEX BY BINARY_INTEGER;
    TYPE SurNameTab IS TABLE OF TblTest.SurName%TYPE INDEX BY BINARY_INTEGER;
    TYPE RidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

    Names NameTab;
    SurNames SurNameTab;
    MyRowId RidTab;
    rec_Rowid Binary_Integer;
    BEGIN

    SELECT Name BULK COLLECT INTO Names FROM (SELECT Name FROM TblTest
    GROUP BY Name ORDER BY COUNT(Name) DESC) WHERE ROWNUM<=200;

    SELECT SurName BULK COLLECT INTO SurNames FROM (SELECT SurName FROM TblTest
    GROUP BY SurName ORDER BY COUNT(SurName) DESC) WHERE ROWNUM<=200;

    SELECT ROWID BULK COLLECT INTO MyRowId FROM TblTest ;

    FOR rec_Rowid IN MyRowId.FIRST..MyRowId.LAST Loop
    UPDATE TblTest
    SET myName = Names(rec_Rowid),
    mySurName = SurNames(rec_Rowid)
    WHERE ROWID = MyRowid(rec_Rowid);


    END LOOP;

    END;
    /

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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.

    Code:
    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
    Last edited by Sameer; 01-08-2003 at 05:33 AM.

  5. #5
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    Thumbs up Thanks.....

    Thank You Very much Deshpande,

    For guidance. It has worked.

    Regards,

    Shailesh

  6. #6
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Glad to know! x-ITB, Mr. Deshpande

  7. #7
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    FOR i IN 1 .. t_schluessel.COUNT
    LOOP
    UPDATE tbl_ITB
    SET vorname = sVorname ,
    name = sName
    WHERE schluessel = t_schluessel (i) + nStart;
    END LOOP;

    can't we use FORALL here?

    Cheers!
    Cheers!
    OraKid.

  8. #8
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Ofcourse we can use, it will further speed-up the procedure. I just skip because while debugging I put dbms_output in between, whereas if when we use FORALL, it doesn't all to put any debugging statement in-between, and then miss to change FOR to FORALL

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width