oracle用了or 重复记录,Oracle大量重复记录的删除

思路:

1.创建视图,查出重复记录.

2. 每10万一次commit,删除重复记录.[@more@]

--创建查询重复记录的视图

create or replace view v_large_table as

select row_id

from (

select rowid row_id,row_number() over (partition by col1 order by rowid ) rn

from large_table

)

where rn <> 1

--循环删除重复记录,10万一次提交.

create or replace procedure del_repeat

as

v_rowid varchar2(100);

v_num number(10);

cursor c1 is select row_id from v_large_table;

begin

open c1;

v_num :=0;

loop

exit when c1%notfound;

fetch c1 into v_rowid;

delete from large_table where rowid=v_rowid and rownum=1;

if v_num=100000 then

commit;

v_num :=0;

else

v_num :=v_num+1;

end if;

end loop;

commit;

close c1;

end;