开发过程中不管是出于需求还是测试,对于数据往往会用到批量操作。
由于踩坑,重点说下批量删除。
对数亿级数据批量删除,往往会踩到坑,这就需要注意啦,一条delete语句,undo段会比较大,未提交是一直受到保护,其他事物的操作会受到影响,执行时间会比较长或挂起,造成表死锁。如何批量删除且不影响其他事物的操作?这就需要尽量将大的事物划分为小的事物去操作,避免其他事物的执行。
declare
cursor mycursor is SELECT ROWID FROM TEST WHERE XXX=XXXX order by rowid; <--------按ROWID排序的Cursor,删除条件是XXX=XXXX,根据实际情况来定。
type rowid_table_type is table of rowid index by pls_integer;
v_rowid rowid_table_type;
BEGIN
open mycursor;
loop
fetch mycursor bulk collect into v_rowid limit 10000; <--------每次处理10000行搜索,也就是每10000行一提交
exit when v_rowid.count=0;
forall i in v_rowid.first..v_rowid.last
delete from test where rowid=v_rowid(i);
commit;
end loop;
close mycursor;
END;
(SQL 游标:
1、把记录集传给游标;
2、打开游标
3、开始循环
4、从游标中取值
5、检查那一行被返回
6、处理
7、关闭循环
8、关闭游标)
下面一个批量插入脚本,每100条记录提交一次。
批量写入脚本:
begin
for i in 1 .. 100000 loop
insert into test_table
(test_ID,
USER_ID,
CODE,
EXCHANGE_TYPE,
OPERATION_TYPE,
CREATED_DATE)
values
(seq_operation_record.nextval,
to_char(i),
'600000',
'SH',
'i',
to_date('2017-01-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss'));
if mod(i, 100) = 0 then
commit;
end if;
end loop;
end;