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

来源：互联网发布：tensorflow 升级编辑：程序博客网时间：2024/04/28 23:34

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.13SQL> SQL> CREATE TABLE blktest (num NUMBER(20), name varchar2(50));Table created.Elapsed: 00:00:00.08SQL> 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.46SQL> exec bulktest;Execution Time (hsecs)---------------------FOR loop: 30361FORALL:   4792PL/SQL procedure successfully completed.Elapsed: 00:06:32.92

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