摘要：如何给Large Delete操作提速近千倍？(六)
本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。
本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。
1.1. 第五次优化——使用FORALL ＋ 原子级操作
这一次的优化过程已经很清晰了，主要是综合了前面几次优化的思想，将他们的优点集中使用，即：
1) 分段处理：将整个大的事务分割为以10000行为单位的小事务，其中，10000这个基数来自于上面使用批量测试时的结果（从删除10000条记录到删除100000条记录，所需要的操作时间已经出现了比较大的倍数关系）。
2) 拆分操作：避开庞大的IN-LIST条件，而是采用原子级的处理。
3) 批量绑定：使用FORALL进行批量绑定，以数组方式处理，大大减少了PL/SQL引擎和SQL引擎的上下文切换，从而提高了处理速度。
现在，首先来构造以10000为单位的FORALL处理过程：
create or replace procedure del_hubei_SSF_forall
as
type ridArray is table of rowid index by binary_integer;
type dtArray is table of varchar2(50) index by binary_integer;
v_rowid ridArray;
v_mid_to_delete dtArray;
begin
select mid,rowid bulk collect into v_mid_to_delete,v_rowid from temp_mid_hubei_bak where rownum<10001;
forall i in 1 .. v_mid_to_delete.COUNT
delete from SSF where mid = v_mid_to_delete(i);
-- DBMS_OUTPUT.PUT_LINE(to_char(v_mid_to_delete.COUNT)||’ records deleted from hubei_SSF !!!’);
forall i in 1 .. v_rowid.COUNT
delete from temp_mid_hubei_bak where rowid = v_rowid(i);
-- DBMS_OUTPUT.PUT_LINE(to_char(v_rowid.COUNT)||’ records deleted from temp_mid_hubei_bak !!!’);
end;
/
然后构造始终按照10000条循环批量删除的过程：
create or replace procedure exec_forall
(
p_RowCount in number, -- Total need to delete rows count
p_ExeCount in number -- Every times need to delete rows count
)
as
n_RowCount number:=0; -- Yet needed to delete rows count
n_ExeTimes number:=0; -- execute times(loop times)
n_delete number:=0; -- really delete rows count
begin
n_RowCount := p_RowCount;
while n_RowCount >0 loop
EXECUTE IMMEDIATE ’begin del_hubei_SUBSREGINFO_forall; end;’;
commit;
if n_RowCount>p_ExeCount then
n_RowCount:= n_RowCount-p_ExeCount;
n_ExeTimes := n_ExeTimes + 1;
else
n_ExeTimes := n_ExeTimes + 1;
n_delete := n_RowCount;
n_RowCount:= n_RowCount-p_ExeCount;
end if;
DBMS_OUTPUT.PUT_LINE(’---------’||to_char(n_ExeTimes)||’-----------’);
n_delete := n_delete+p_ExeCount*(n_ExeTimes-1);
end loop;
DBMS_OUTPUT.PUT_LINE(’Full Finished!!!’);
DBMS_OUTPUT.PUT_LINE(’Totally ’||to_char(n_delete)||’ records deleted. !!!’);
end;
/
好了，现在来测试一下，这次优化的成果吧：
首先，看一下临时表的记录数目：
SQL> select count(*) temp_mid_hubei from temp_mid_hubei_bak;
TEMP_MID_HUBEI
--------------
1444661
Elapsed: 00:00:02.41
SQL>
再检查一下业务大表的记录数目;
SQL> SELECT COUNT(*) SSF FROM SSF;
SSF
-----------
83446270
ELAPSED: 00:04:53.27
SQL>
首先，我们删除500000行记录，看看需要多少时间完成：
SQL> exec exec_forall(500010,10001);
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------1-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------2-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------3-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------4-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------5-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------6-----------
。。。。。。。。。。。（这里省略掉了一些雷同的输入）
---------50-----------
Full Finished!!!
Totally 500010 records deleted. !!!
PL/SQL procedure successfully completed.
Elapsed: 00:02:57.78
SQL>
这是个惊人的数字，2分57秒！！
于是再来删除90万试试看：
SQL> exec exec_forall(900010,10001);
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------1-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------2-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------3-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------4-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------5-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------6-----------
。。。。。。。。。
---------86-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------87-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------88-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------89-----------
10000 records deleted from hubei_SSF !!!
10000 records deleted from temp_mid_hubei_bak !!!
---------90-----------
Full Finished!!!
Totally 900010 records deleted. !!!
PL/SQL procedure successfully completed.
Elapsed: 00:02:33.47
SQL>
SQL> select count(*) temp_mid_hubei from temp_mid_hubei_bak;
TEMP_MID_HUBEI
--------------
34661
Elapsed: 00:00:00.46
SQL>
删除90万的数据只需要2分33秒！！和删除50万数据量相比，使用的操作时间差不多。
到此，此次任务已经可以圆满完成了（将所有的操作使用这种优化思想写成脚本，很快就可以完成任务）。