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

http://www.eygle.com/archives/2005/11/bulk_binds_forall.html

通常在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循环性能大大提高。