Tuesday, January 29, 2008

Another way of manipulating large amount of data in oracle (Remove duplicate)

Below example explains, 1 way to remove duplicate data. tmp_data table contains 6millions of record, but with duplicate. Table tmp_duplicate contains all duplicates data that exists in tmp_data.

create or replace procedure delete_duplicate is

cursor c is select sn from tmp_data a where a.sn in (select sn from tmp_duplicate);

type c1_type is table of c%rowtype;

l_c1 c1_type;


open c;


fetch c bulk collect into l_c1 LIMIT 1000;

for i in 1 .. l_c1.count


--Expecting to do the delete

dbms_output.put_line('Delete this ' || l_c1(i).sn);

end loop;


exit when c%notfound;

end loop;

close c;

end delete_duplicate;

No comments: