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

本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。

 

 

1.1. 第二次优化——分段操作

这次优化的思想仅仅是通过rownum将完整的操作分成若干段，设定每次（每段）只操作指定数量的行，删除完成后立即提交。

       该过程如下：

CREATE OR REPLACE PROCEDURE del_hubei_ssf (    p_count   IN   VARCHAR2             -- Commit after delete How many records ) AS    PRAGMA AUTONOMOUS_TRANSACTION;    sql_stat   VARCHAR2 (1000) := '';    n_delete   NUMBER          := 0; BEGIN    /** 3. delete data from the hubei  SSF **/    DBMS_OUTPUT.put_line ('3. Start delete from the hubei  SSF!!!');        WHILE 1 = 1    LOOP       EXECUTE IMMEDIATE 'DELETE /*+ RULE */ from SSF WHERE mid IN (SELECT mid FROM temp_mid_hubei) and rownum<=:rn'                   USING p_count;           IF SQL%NOTFOUND       THEN          EXIT;       ELSE          n_delete := n_delete + SQL%ROWCOUNT;       END IF;           COMMIT;       DBMS_OUTPUT.put_line (sql_stat);       DBMS_OUTPUT.put_line (TO_CHAR (n_delete) || ' records deleted ...');    END LOOP;        COMMIT;    DBMS_OUTPUT.put_line ('Full Finished!!!');    DBMS_OUTPUT.put_line (   'Totally '                          || TO_CHAR (n_delete)                          || ' records deleted from hubei_SSF  !!!'                         ); EXCEPTION    WHEN OTHERS    THEN       DBMS_OUTPUT.put_line (SQLERRM); END; /

 

 

根据上面小表的测试结果，删除10000行的操作应该在几分钟之内完成，那么删除百万行的记录，应该在20个小时左右应该可以有结果了，于是决定放心的再放一个专门利用上面的存储过程来进行大量删除的脚本，下班前放到后台跑，准备第二天来上班时间来拿结果。

次日午后，我检查nohup.out，奇怪，居然还没有完成的信息，看来，上面的问题有了答案，对于越大的表和越大的结果集来说，随着操作记录的成倍增加，操作时间将以一定的倍数增加，所以仅仅这样优化单个大表操作的语句是不能解决问题的。

 

 

于是有了第三个优化思路，拆分DELETE操作，将整个DELETE的操作拆分成原子级。