如何更快地批量数据处理？

在批量处理数据时，比如从上亿条记录的表中删除上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，而不是通过索引扫描，性能当然会略有提升，但总体来讲，比后半部分的代码，性能上还是大大不如……