更快地批量数据处理 by ace yangtingkun

在批量处理数据时，比如从上亿条记录的表中删除上1000万条数据，不仅要考虑对undo的影响，还要考虑对redo，对磁盘i/o，锁、阻塞等的影响，在某种意思上讲，同样的任务，执行的sql次越少，总的消耗的时间就越少。。。

 

create table tmp_object1 as select * from dba_objects;
create index idx_object1_name on tmp_object1(object_name);
create table tmp_object2 as select * from dba_objects;

alter session set events '10046 trace name context forever, level 12';

declare
cursor cur_obj1 is select object_name from tmp_object1;

type v_array is table of tmp_object1.object_name%type;
v_obj1 v_array;
begin
open cur_obj1;
loop
fetch cur_obj1 bulk collect into v_obj1 limit 10000;

forall i in 1..v_obj1.count
delete from tmp_object1 where object_name = v_obj1(i);
commit;

exit when cur_obj1%notfound;
end loop;
close cur_obj1;
end;
/
alter session set events '10046 trace name context off';

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------
Parse 16 0.00 0.00 0 2 0 0
Execute 26 9.23 9.03 254 52778 121592 51712
Fetch 39 0.07 0.07 1 1363 0 51735
------- ------ -------- ---------- ---------- ---------- ---------- -------
total 81 9.31 9.11 255 54143 121592 103447

Misses in library cache during parse: 5
Misses in library cache during execute: 4

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ----------
db file sequential read 254 0.00 0.00

alter session set events '10046 trace name context forever, level 12';

declare
v_cnt number;
begin
select count(*) into v_cnt
from tmp_object2;

v_cnt := floor(v_cnt/10000) + 1;
for i in 1..v_cnt loop
delete from tmp_object2 where rownum <= 10000;
commit;
end loop;
end;
/
alter session set events '10046 trace name context off';

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------
Parse 14 0.00 0.00 0 2 0 0
Execute 24 1.15 1.70 0 2785 57665 51712
Fetch 31 0.04 0.04 712 865 0 22
------- ------ -------- ---------- ---------- ---------- ---------- -------
total 69 1.20 1.75 712 3652 57665 51734

Misses in library cache during parse: 3
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ----------
db file sequential read 17 0.00 0.00
db file scattered read 67 0.00 0.01
log file switch completion 3 0.02 0.04
log buffer space 1 0.11 0.11

说明：在上面的前半部分示例代码中，若把object_name = v_obj(i)改成rowid = v_obj(i)，删除数据时用rowid，而不是通过索引扫描，性能当然会略有提升，但总体来讲，比后半部分的代码，性能上还是大大不如。。。