bulk批量删除数据
bulk批量删除数据
1. 案列介绍
需要在一个1亿行的大表中，删除1千万行数据
需求是在对数据库其他应用影响最小的情况下，以最快的速度完成
如果业务无法停止的话，可以参考下列思路：
根据ROWID分片、再利用Rowid排序、批量处理、回表删除
在业务无法停止的时候，选择这种方式，的确是最好的
一般可以控制在每一万行以内提交一次，不会对回滚段造成太大压力
我在做大DML时，通常选择一两千行一提交
选择业务低峰时做，对应用也不至于有太大影响
2. 代码实现
测试环境
drop table t_emp purge;
create table t_emp as select * from emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
insert into t_emp select * from t_emp;
commit;
具体代码
version 1.0
declare
cursor c_rowid is
select rowid from t_emp where deptno = 30 order by rowid; --data need to be deleted
type type_rowid is table of rowid index by pls_integer;
v_tab_rowid type_rowid;
v_num number := 0;
begin
open c_rowid;
loop
fetch c_rowid bulk collect
into v_tab_rowid limit 50;
--exit when c_rowid%notfound;
forall i in v_tab_rowid.first .. v_tab_rowid.last
delete from t_emp where rowid = v_tab_rowid(i);
commit;
v_num := v_num + v_tab_rowid.count;
exit when c_rowid%notfound;
endloop;
close c_rowid;
dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd:') || 'delete rows '||
to_char(v_num));
end;
/
version 2.0
declare
cursor c_rowid is
select rowid from t_emp where deptno = 30 order by rowid; --data need tobe deleted
type type_rowid is table of rowid index by pls_integer;
v_tab_rowid type_rowid;
v_num number := 0;
begin
open c_rowid;
loop
fetch c_rowid bulk collect
into v_tab_rowid limit 50;
exit when v_tab_rowid.count=0;
forall i in v_tab_rowid.first .. v_tab_rowid.last
delete from t_emp where rowid = v_tab_rowid(i);
commit;
v_num := v_num + v_tab_rowid.count;
endloop;
close c_rowid;
dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd:') || 'delete rows '||
to_char(v_num));
end;
/