« 无尽的爱(Endless Love) - 美丽的神话 | Blog首页 | NetAPP + Linux iSCSI配置step-by-step »
批量绑定(bulk binds):FOR循环与FORALL的性能比较
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2005/11/bulk_binds_forall.html
通常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding）。链接：https://www.eygle.com/archives/2005/11/bulk_binds_forall.html
批量绑定（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循环性能大大提高。
历史上的今天...
>> 2008-11-13文章:
>> 2006-11-13文章:
历史上的今天...
>> 2008-11-13文章:
>> 2006-11-13文章:
By eygle on 2005-11-13 20:28 | Comments (6) | SQL.PLSQL | 528 |
我的测试怎么这样啊？
SQL> CREATE TABLE blktest (num NUMBER(20), name varchar2(50));
Table created
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 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 ('FORALL: ' || TO_CHAR (t3 - t2));
46 DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1));
47 END;
48 /
Procedure created
SQL> exec bulktest;
Execution Time (hsecs)
---------------------
FORALL: 10181
FOR loop: 7272
查一下其他方面,看是不是日志切换或其他因素影响了结果.
或者多测试几次看看结果.
您好，刚看完您讲的oracle之路和数据库优化，想问个问题：
我执行你上面的例子，怎么看不到DBMS_OUTPUT.put_line输出的信息？怎么设置？多谢了。呵呵
先
set serveroutput on
我的结果,环境 win2003 oracle 10g
scott@ORA10G>exec bulktest;
Execution Time (hsecs)
---------------------
FOR loop: 12199
FORALL: 9027
PL/SQL 过程已成功完成。
我在xp，oracle9i，用pl/sql developer执行结果：
Execution Time (hsecs)
---------------------
FOR loop: 3186
FORALL: 568