在处理大量数据的增删改时,一次性提交大量数据可能会导致undo表空间使用率达到100%,从而影响数据库整体稳定性,因此在处理大批量数据时,建议进行分批次提交。
同时,也要明白采用分批操作并不能提高执行速度,执行效率不如单条DML语句,另外分批插入可以减少对undo空间的占用,但频繁的提交,可能会导致前面提交的undo空间被其他事务占用而可能导致ORA-0155错误,若分批操作中间半截失败掉,会将你数据库置于一种未知的状态(DELETE操作不会出现这种情况)。
1.分批次update
--创建试验表
create table u_ocp.t1 as select object_name from dba_objects where 1=2;
--将t1表中的数据更新为小写
declare
--定义游标
cursor c_cur is
select rowid, object_name
from u_ocp.t1;
--基于游标的嵌套表类型
type c_cur_rowid is table of rowid;
type c_cur_varchar is table of t1.object_name%type;
--声明变量
v_rowid c_cur_rowid;
v_names c_cur_varchar;
begin
open c_cur;
loop
--限制一次提取的数据量()到v_row
fetch c_cur bulk collect
into v_rowid, v_names limit 10000;
forall i in v_rowid.first .. v_rowid.last
--修改数据
update t1 set t1.object_name=lower(v_names(i)) where rowid=v_rowid(i);
commit;
exit when c_cur%notfound;
end loop;
--关闭游标
close c_cur;
end;
/
2.分批次delete
--批量删除数据
declare
--定义游标
cursor c_cur is
select rowid
from u_ocp.t1 z
where 1=1;
--基于游标的嵌套表类型
type c_cur_type is table of c_cur%rowtype;
--声明变量
v_row c_cur_type;
begin
open c_cur;
loop
--限制一次提取的数据量()到v_row
fetch c_cur bulk collect
into v_row limit 10000;
exit when v_row.count = 0;
forall i in v_row.first .. v_row.last
--修改数据
delete from u_ocp.t1 a
where rowid = v_row(i).rowid;
commit;
end loop;
--关闭游标
close c_cur;
end;
/
3.分批次insert
--将t1中的数据分批insert到t2
declare
--定义游标
cursor c_cur is
select *
from u_ocp.t1;
--基于游标的嵌套表类型
type c_cur_type is table of c_cur%rowtype;
--声明变量
v_row c_cur_type;
begin
open c_cur;
loop
--限制一次提取的数据量()到v_row
fetch c_cur bulk collect
into v_row limit 10000;
exit when v_row.count = 0;
forall i in v_row.first .. v_row.last
--insert数据
insert into u_ocp.t2 values v_row(i);
commit;
end loop;
--关闭游标
close c_cur;
end;
/
经过实验比对,以上脚本每次提交10000行数据比100行数据快出20%左右,当然究竟快出多少根据各个环境的不同会有所差异,仅供参考。