-
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;
/
-------------------------------------------------
-
-
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;
/
-
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
-
Thanks.....
Thank You Very much Deshpande,
For guidance. It has worked.
Regards,
Shailesh
-
Glad to know! x-ITB, Mr. Deshpande :D
-
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!
-
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
