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

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

1.   背景描述

1.1. 任务描述

这个任务是需要从一系列大表中清理3个省的大批过时数据，具体的清理过程简单的说就是：

首先根据不同的miscid的值创建不同的临时表，类似于：

 

CREATE TABLE temp_mid AS SELECT mid FROM ssr WHERE SUBSTR(ssid,1,7) IN (SELECT prefixnum FROM prefix WHERE mcid='0012');

      

然后通过这个临时表连接另一个大表，做删除工作：

DELETE SSF       WHERE mid IN (SELECT mid                       FROM TEMP_MID_HUBEI);

 

上述任务根据不同的关键字，需要执行几十次，如果不加任何优化的话，每一次都需要执行几十个小时。由于需求、操作和优化思路大体相同，下面我们就以上面的例子详细说说实际应用中如何一步步优化提速到近千倍的过程。

 

 

1.2. 数量级统计和描述

首先统计这个操作涉及到的几张表：

 

SELECT COUNT(*) FROM PREFIX;   SELECT COUNT(*) SSR FROM SSR;   SELECT COUNT(*) SSF FROM SSF;   SELECT COUNT(*) AS SSF_0012 FROM SSF WHERE MID IN (SELECT MID FROM TEMP_MID_HUBEI);

 

上述脚本的执行过程如下（请注意，由于创建临时表 TEMP_MID_HUBEI 的过程比较简单，因此这里没有赘述，仅仅是从建立临时表后的删除操作开始分析的）：

 

SQL> @LUNAR.SQL   PREFIX ----------      51854   ELAPSED: 00:00:00.14   SSF -----------    83446270   ELAPSED: 00:04:53.27   SSR ----------   43466645   ELAPSED: 00:03:08.00   SSF_0012 ----------------           131098   ELAPSED: 00:00:57.02 SQL>

 

 

我们注意到，这里面，我们需要做的是从一个8300多万行的大表中，通过和一个130多万行的表进行连接并删除其中的大部分数据。整个操作的过程，要求所有的表都可以实时访问，并且除了我们手工建立的临时表（TEMP_MID_HUBEI）以外，其他的表都可以实时访问和修改。

 

 

 

2.   背景知识——Bulk Binding

在下面的优化过程中，我们使用了批量绑定（Bulk Binding）的思想，因此首先对这一知识作些解释。

 

 

2.1. 什么是Bulk Binding？

在sql语句中（动态地）给PL/SQL变量赋值叫做绑定（Binding）。一次绑定一个完整的集合叫做批量绑定（Bulk Binding）。

从Oracle 8i开始，在PL/SQL可以使用两个新的数据操纵语言（DML）语句：BULK COLLECT和FORALL。这两个语句在PL/SQL内部按数组进行数据处理。

 

 

2.2. Bulk binds的优点是什么呢？

       批量绑定（Bulk binds）通过最小化在PL/SQL和SQL引擎之间的上下文切换提高了性能，它以一个完整的集合（如，varray, nested tables, index-by table, or host array）为单位（一批一批的）向前或者向后绑定变量。在Oracle 8i以前，每个SQL语句的执行需要在PL/SQL和SQL引擎之前切换上下文，使用绑定变量后，就只需要一次上下文切换。

其中，BULK COLLECT提供对数据的高速检索，FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。

 

 

2.3. 如何进行批量绑定（Bulk Binds）？

绑定变量包扩下面两个部分：

1)        输入集合（collections），使用FORALL语句，一般用来改善DML（INSERT、UPDATE和DELETE) 操作的性能；

2)        输出集合（collections），使用BULK COLLECT子句；一般用来提高查询（SELECT）的性能。

 

2.3.1.    输入集合(FORALL)

输入集合是数据通过PL/SQL引擎到SQL引擎去执行INSERT, UPDATE, DELETE语句。输入集合使用FORALL语句，下面是FORALL的语法：

 

FORALL index IN lower_bound..upper_bound sql_statement;

 

2.3.2.    FOR.. LOOP语句和FORALL的比较

例1（example1）：分别使用传统的FOR .. LOOP操作和我们这里介绍的FORALL 操作 向lunartest表中加载1000000条记录，对比一下他们的执行效率。

 

测试过程如下：

首先创建一个用来记录操作时间的存储过程 get_time ：

CREATE OR REPLACE PROCEDURE get_time (t OUT NUMBER) IS BEGIN    SELECT TO_CHAR (SYSDATE, 'SSSSS')      INTO t      FROM DUAL; END;

 

然后创建一个空表，分别使用FOR .. LOOP和FORALL .. LOOP插入数据，并记录和输出操作时间：

SQL> conn lunar/lunar Connected. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE lunartest (pnum NUMBER(20), pname varchar2(50));   Table created.   Elapsed: 00:00:00.00 SQL> Create Or Replace PROCEDURE BulkTest IS   2     TYPE NumTab IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;   3     TYPE NameTab IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER;   4     pnums NumTab;   5     pnames NameTab;   6     t1 CHAR(5);   7     t2 CHAR(5);   8     t3 CHAR(5);   9  BEGIN  10     FOR j IN 1..1000000 LOOP   -- load index-by tables  11        pnums(j) := j;  12        pnames(j) := 'Part No. ' || TO_CHAR(j);  13     END LOOP ;  14  15     get_time(t1);  16  17     FOR i IN 1..1000000 LOOP -- use FOR loop  18        INSERT INTO lunartest VALUES (pnums(i), pnames(i));  19     END LOOP ;  20  21     get_time(t2);  22  23     FORALL i IN 1..1000000  --use FORALL statement  24        INSERT INTO lunartest VALUES (pnums(i), pnames(i));  25     get_time(t3);  26  27     DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');  28     DBMS_OUTPUT.PUT_LINE('---------------------');  29     DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));  30     DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR(t3 - t2));  31  END;  32  /   Procedure created.   Elapsed: 00:00:00.00 SQL> exec BulkTest; Execution Time (secs) --------------------- FOR loop: 110 FORALL:   54   PL/SQL procedure successfully completed.   Elapsed: 00:03:24.07 SQL> select sum(bytes/1024/1024) MB from user_segments where segment_name='LUNARTEST';           MB ----------         57   Elapsed: 00:00:11.06 SQL>

 

这里我们注意到使用FOR .. LOOP语句插入1000000条记录，需要110秒；而使用FORALL语句只需要54秒。

 

2.3.3.    如何处理回滚？

对于回滚的处理，FORALL操作可以自动完成，也就是说，如果一个FORALL 语句执行失败，那么Oracle会基于隐式的SAVE POINT一次回滚SQL语句中先前执行的部分。

 

2.3.4.    输出集合s

输出集合是数据作为一个通过SQL引擎到PL/SQL引擎的（SELECT 或者 FETCH的）结果集。

输出集合通过在SELECT INTO, FETCHINTO和RETURNING INTO子句中加入BULK COLLECT子句实现，下面是BULK COLLECT子句的语法：

 

... BULK COLLECT INTO collection_name[, collection_name] ....

 

 

2.3.5.    在SELECT INTO中使用BULK COLLECT

这里我们结合一个实例来理解一下在SELECT INTO 语句中批量绑定的使用：

SQL> conn lunar/lunar Connected. SQL> SET SERVEROUTPUT ON SQL> Create Or Replace Procedure lunartest2 Is   2     TYPE NumTab IS TABLE OF emp.empno%TYPE;   3     TYPE NameTab IS TABLE OF emp.ename%TYPE;   4     enums NumTab; -- no need to initialize   5     names NameTab;   6  BEGIN   7     SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;   8     FOR i in enums.FIRST..enums.LAST LOOP   9        DBMS_OUTPUT.PUT_LINE(enums(i) || ' ' || names(i));  10     END LOOP ;  11  END;  12  /   Procedure created.   Elapsed: 00:00:00.08 SQL>

 

这里我们看到，使用SELECT .. BULK COLLECT INTO的方法和传统的SELECT .. INTO的语法基本上变化不大。现在我们看一下执行结果：

 

SQL>  select empno,ename from emp;        EMPNO ENAME ---------- ----------       7369 SMITH       7499 ALLEN       7521 WARD       7566 JONES       7654 MARTIN       7698 BLAKE       7782 CLARK       7788 SCOTT       7839 KING       7844 TURNER       7876 ADAMS       7900 JAMES       7902 FORD       7934 MILLER   14 rows selected.   Elapsed: 00:00:00.00 SQL> exec lunartest2; 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER   PL/SQL procedure successfully completed.   Elapsed: 00:00:00.00 SQL>

      

       如果我们这里需要输出的记录不是14条，而是140万条甚至更多，那么BULK COLLECT的就会发挥出强悍的优势，这里我们仅仅是在功能上得到验证。

 

 

2.3.6.    在FETCH INTO中使用BULK COLLECT

现在，我们来看看在FETCH INTO 语句中如何使用批量绑定：

SQL> conn lunar/lunar Connected. SQL> SET SERVEROUTPUT ON SQL> Create Or Replace Procedure lunartest3 Is   2     TYPE NameTab IS TABLE OF emp.ename%TYPE;   3     TYPE SalTab IS TABLE OF emp.sal%TYPE;   4     names NameTab;   5     sals SalTab;   6     CURSOR c1 IS SELECT ename, sal FROM emp;   7  BEGIN   8     OPEN c1;   9     FETCH c1 BULK COLLECT INTO names, sals;  10     FOR i IN names.FIRST..names.LAST LOOP  11        DBMS_OUTPUT.PUT_LINE(names(i) || ' ' || sals(i));  12     END LOOP ;  13     CLOSE c1;  14  END;  15  /   Procedure created.   Elapsed: 00:00:00.01 SQL> select ename,sal from emp;   ENAME             SAL ---------- ---------- SMITH             800 ALLEN            1600 WARD             1250 JONES            2975 MARTIN           1250 BLAKE            2850 CLARK             2450 SCOTT            3000 KING             5000 TURNER           1500 ADAMS             1100 JAMES             950 FORD             3000 MILLER           1300   14 rows selected.   Elapsed: 00:00:00.00 SQL> exec lunartest3; SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300   PL/SQL procedure successfully completed.   Elapsed: 00:00:00.00 SQL>

 

这里有一点需要特比注意，bulk-tech不能从游标（cursor）插入到一个记录的集合。

 

 

2.3.7.    使用新的游标（cursor）属性实现BULK COLLECT

从Oracle 9i开始，Oradcle又提供了新的bulk binds游标属性，即%BULK_ROWCOUNT，其语法为：

 

IF SQL%BULK_ROWCOUNT(i) = ... THEN …… ENDIF;

 

该游标属性的使用方法和含义与传统的SQL% ROWCOUNT基本相同。

       下面我们就结合一个实例，了解forall的强大作用和使用的注意事项。