** 本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。 **
** 本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。 **
1.1. 第五次优化—— 使用 FORALL ＋ 原子级操作
这一次的优化过程已经很清晰了，主要是综合了前面几次优化的思想，将他们的优点集中使用，即：
分段处理：将整个大的事务分割为以 10000 行为单位的小事务，其中， 10000 这个基数来自于上面使用批量测试时的结果（从删除 10000 条记录到删除 100000 条记录，所需要的操作时间已经出现了比较大的倍数关系）。
拆分操作：避开庞大的 IN-LIST 条件，而是采用原子级的处理。
批量绑定：使用 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 万数据量相比，使用的操作时间差不多。
到此，此次任务已经可以圆满完成了（将所有的操作使用这种优化思想写成脚本，很快就可以完成任务）。