如何给Large Delete操作提速近千倍？(五)

 

1.1. 第四次优化——使用FORALL 处理批量作业

上面我们已经介绍了批量作业的一些概念，这里不再赘述。

这次优化的思想主要是使用FORALL的思路来改写“第三次优化——拆分DELETE操作”，具体的修改如下：

SQL> l

  1  create or replace procedure del_hubei_SSR_forall

  2  as

  3       type ridArray is table of rowid index by binary_integer;

  4       type dtArray is table of varchar2(50) index by binary_integer;

  5

  6       v_rowid ridArray;

  7       v_mid_to_delete dtArray;

  8

  9  begin

 10       select mid,rowid bulk collect into v_mid_to_delete,v_rowid from temp_mid_hubei where rownum<11;

 11

 12      forall i in 1 .. v_mid_to_delete.COUNT

 13           delete from SSR where mid = v_mid_to_delete(i);

 14

 15             DBMS_OUTPUT.PUT_LINE('Full Finished!!!');

 16             DBMS_OUTPUT.PUT_LINE('Totally '||to_char(v_mid_to_delete.COUNT)||' records deleted from hubei_SSR   !!!');

 17

 18       forall i in 1 .. v_rowid.COUNT

 19           delete from temp_mid_hubei where rowid = v_rowid(i);

 20

 21             DBMS_OUTPUT.PUT_LINE('Full Finished!!!');

 22             DBMS_OUTPUT.PUT_LINE('Totally '||to_char(v_rowid.COUNT)||' records deleted from temp_mid_hubei   !!!');

 23

 24* end;

SQL> /

 

Procedure created.

 

Elapsed: 00:00:00.07

SQL>

SQL>  exec del_hubei_SSR_forall;

Full Finished!!!

Totally 10 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 10 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.16

SQL>

 

 

这里应用了上面我们说过的SELECT BULK COLLECT INTO 方法来进行批量绑定，根据以10条记录为一组的测试结果表名，速度比较理想，于是再测1000条记录的情况：

 

SQL> exec del_hubei_SSR_forall;

Full Finished!!!

Totally 1000 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 100 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.66

SQL>

 

 

测试结果还是比较稳定，于是再测10000和100000条记录的情况：

 

SQL> exec del_hubei_SSR_forall;

Full Finished!!!

Totally 10000 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 10000 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:04.07

SQL>

SQL> exec del_hubei_SSR_forall;

Full Finished!!!

Totally 100000 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 100000 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:03:51.29

SQL>

 

这里我们注意到，完成删除10000记录需要4秒中，而完成100000条记录的删除需要将近4分钟，也就是说，从删除10000条记录到删除100000条记录，所需要的操作时间已经出现了比较大的倍数关系。

 

下面再测试一下50万条记录的情况：

SQL> exec del_hubei_SSR_forall;

Full Finished!!!

Totally 509555 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 509555 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:39:04.41

SQL>

 

这里，删除500000条记录已经需要将近40分钟了！

到这里，基本上可以得出结论，使用批量删除虽然可以解决一定的问题，但是如果需要操作的数组太大，那么执行结果会打打折扣，这个是不难理解的。

于是，第五次优化的思路就产生了：

1)        分段处理

2)        拆分操作

3)        批量绑定