如何给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万数据量相比，使用的操作时间差不多。

 

 

到此，此次任务已经可以圆满完成了（将所有的操作使用这种优化思想写成脚本，很快就可以完成任务）。