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

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

通常在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

2IS

3 TYPE numtab IS TABLE OF NUMBER (20)

4INDEX BY BINARY_INTEGER;

5

6 TYPE nametab IS TABLE OF VARCHAR2 (50)

7INDEX BY BINARY_INTEGER;

8

9 pnumsnumtab;

10 pnames nametab;

11 t1 NUMBER;

12 t2 NUMBER;

13 t3 NUMBER;

14BEGIN

15 FOR j IN 1 .. 1000000

16 LOOP

17pnums (j) := j;

18pnames (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

27INSERT 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

36INSERT 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));

47END;

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航