批量绑定(bulk binds):FOR循环与FORALL的性能比较

 通常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding）。

批量绑定（Bulk binds）可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.

批量绑定（Bulk binds）包括：
(i) Input collections, use the FORALL statement，一般用来改善DML（INSERT、UPDATE和DELETE) 操作的性能
(ii) Output collections, use BULK COLLECT clause，一般用来提高查询（SELECT）的性能

FORALL的语法如下：

FORALL index IN lower_bound..upper_bound sql_statement;

下面是一个简单测试，用以说明FORALL与FOR循环的性能差异。

SQL> drop table blktest;

Table dropped.

Elapsed: 00:00:00.13
SQL>
SQL> CREATE TABLE blktest (num NUMBER(20), name varchar2(50));

Table created.

Elapsed: 00:00:00.08
SQL>
SQL> CREATE OR REPLACE PROCEDURE bulktest
  2  IS
  3     TYPE numtab IS TABLE OF NUMBER (20)
  4        INDEX BY BINARY_INTEGER;
  5 
  6     TYPE nametab IS TABLE OF VARCHAR2 (50)
  7        INDEX BY BINARY_INTEGER;
  8 
  9     pnums    numtab;
 10     pnames   nametab;
 11     t1       NUMBER;
 12     t2       NUMBER;
 13     t3       NUMBER;
 14  BEGIN
 15     FOR j IN 1 .. 1000000
 16     LOOP
 17        pnums (j)         := j;
 18        pnames (j)        := 'Seq No. ' || TO_CHAR (j);
 19     END LOOP;
 20 
 21     SELECT DBMS_UTILITY.get_time
 22       INTO t1
 23       FROM DUAL;
 24 
 25     FOR i IN 1 .. 1000000
 26     LOOP
 27        INSERT INTO blktest
 28             VALUES (pnums (i), pnames (i));
 29     END LOOP;
 30 
 31     SELECT DBMS_UTILITY.get_time
 32       INTO t2
 33       FROM DUAL;
 34 
 35     FORALL i IN 1 .. 1000000
 36        INSERT INTO blktest
 37             VALUES (pnums (i), pnames (i));
 38 
 39     SELECT DBMS_UTILITY.get_time
 40       INTO t3
 41       FROM DUAL;
 42 
 43     DBMS_OUTPUT.put_line ('Execution Time (hsecs)');
 44     DBMS_OUTPUT.put_line ('---------------------');
 45     DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1));
 46     DBMS_OUTPUT.put_line ('FORALL:   ' || TO_CHAR (t3 - t2));
 47  END;
 48  /

Procedure created.

Elapsed: 00:00:01.46
SQL> exec bulktest;
Execution Time (hsecs)
---------------------
FOR loop: 30361
FORALL:   4792

PL/SQL procedure successfully completed.

Elapsed: 00:06:32.92
 

我们可以看到FORALL较FOR循环性能大大提高。

作者： eygle | English Version 【转载时请以超链接形式标明文章 出处 和作者信息及 本声明 】
链接： http://www.eygle.com/archives/2005/11/bulk_binds_forall.html

 

通过触发器来观察二者的区别：

SQL> CREATE TABLE TTT (ID NUMBER);

表已创建。

SQL> CREATE OR REPLACE TRIGGER TRI_TTT BEFORE INSERT ON TTT
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('A');
4 END;
5 /

触发器已创建

SQL> SET SERVEROUT ON
SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FOR I IN 1..V_NUM.COUNT LOOP
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END LOOP;
9 END;
10 /
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A

PL/SQL 过程已成功完成。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FORALL I IN 1..V_NUM.COUNT
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END;
9 /
A

PL/SQL 过程已成功完成。

从触发器的除非动作上可以看出，FOR ALL语法和FOR LOOP的区别，FOR ALL对INSERT语句只调用了一次。

从这一点上，FOR ALL语法于INSERT INTO SELECT语法更为相似。但FOR ALL又和INSERT INTO SELECT有着本质的区别：

SQL> TRUNCATE TABLE TTT;

表被截断。

SQL> ALTER TABLE TTT MODIFY ID NUMBER(3);

表已更改。

SQL> INSERT INTO TTT SELECT 994 + ROWNUM FROM TAB;
INSERT INTO TTT SELECT 994 + ROWNUM FROM TAB
*第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度

SQL> SELECT * FROM TTT;

未选定行

INSERT INTO SELECT语法属于一条语句，根据Oracle的语句级回滚，当插入由于个别数据发生错误的时候，整个插入语句被回滚。

但对于FOR ALL语句，虽然Oracle只执行了INSERT语句一次，但是，如果发生了错误，是可以捕获的，且错误发生之间的操作是可以保留下来的。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 V_NUM(5) := 1000;
7 BEGIN
8 FORALL I IN 1..V_NUM.COUNT
9 INSERT INTO TTT VALUES (V_NUM(I));
10 EXCEPTION
11 WHEN OTHERS THEN
12 COMMIT;
13 END;
14 END;
15 /

PL/SQL 过程已成功完成。

SQL> SELECT * FROM TTT;

ID
----------
1
2
3
4

从这一点看，FOR ALL语法和INSERT INTO SELECT又有着本质的区别。个人感觉FOR ALL语法和Oracle的OCI中数组绑定语法十分类似。二者都采用数据绑定变量的方式，通过调用一次SQL，将整个数组的内容提交给Oracle，并且出现错误后，可以通过捕获错误的方式保留出错前已经进行的修改。

个人认为，FOR ALL语法和OCI的数组绑定具有相同的内部机制。二者分别为PL/SQL和OCI提供了相同的批量处理功能。

来源：http://yangtingkun.itpub.net/post/468/198828

 

注意事项：

不能在FORALL语句中使用SELECT ... BULK COLLECT语句。
forall 后面的DML语句里面不能有集合变量的元素的字段。