FORALL 提高批绑定性能

一、如何使用批挷定提高性能(How Do Bulk Binds Improve Performance)
在 PL/SQL和 SQL引擎(engines)中,太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL 语句时。而使用批挷定能显著提高性能。下图显示 PL/SQL 引擎 和 SQL引擎之间的 context switches:(PL/SQL引擎执行存过语句仅发送SQL 语句到 SQL引擎,SQL引擎执行语句后返回数据给 PL/SQL

引擎)

PL/SQL 引擎发送一次 SQL语句给 SQL引擎,在 SQL引擎中则为范围中每个 index 数字执行一次 SQL语句。

PL/SQL 挷定操作包含以下三类:
in-bind: When a PL/SQL variable or host variable is stored in the database by an

INSERT or UPDATE statement.
out-bind:When a database value is assigned to a PL/SQL variable or a host variable

by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.define: WhenadatabasevalueisassignedtoaPL/SQLvariableorahostvariable

         by a SELECT or FETCH statement.

在 SQL语句中,为 PL/SQL变量指定值称为挷定(binding),
DML 语句能传递所有集合元素到一个单个操作中,这过程称为批挷定(bulk binding)。如果集合有 20 个元素,批挷定让你用单个操作等效于执行与20 个 SELECT,INSERT,

UPDATE 或 DELETE语句。这个技术通过减少在 PL/SQL 和 SQL引擎(engines)间的上下文

切换来提高性能。批挷定包括:
1.带INSERT, UPDATE, and DELETE语句的批挷定:在 FORALL 语句中嵌入 SQL语句2.带SELECT语句的批挷定:在 SELECT 语句中用 BULK COLLECT语句代替 INTO

下边的例子分别用 FOR和 FORALL进行数据插入,以显示用批挷定的对性能的提高:SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
Table created.

SQL> DECLARE

2. 2  TYPE NumTab IS TABLE OF parts.pnum%TYPE INDEX BY BINARY_INTEGER;

3. 3  TYPE NameTab IS TABLE OF parts.pname%TYPE INDEX BY BINARY_INTEGER;

4. 4  pnums NumTab;

5. 5  Pnames NameTab;

6. 6  t1 NUMBER;

7. 7  t2 NUMBER;

8. 8  t3 NUMBER;

9. 9  BEGIN

10. 10  FOR i IN 1..500000 LOOP

11.  11      pnums(i) := i;
    

12.  12      pnames(i) := 'Part No.'||to_char(i);
    

13. 13  END LOOP;

14. 14  t1 := dbms_utility.get_time;

15

16. 16  FOR i IN 1..500000 LOOP

17.  17      INSERT INTO parts VALUES(pnums(i),pnames(i));
    

18. 18  END LOOP;

19. 19  t2 := dbms_utility.get_time;

20

21. 21  FORALL i IN 1..500000

22.  22      INSERT INTO parts VALUES(pnums(i),pnames(i));
    

23. 23  t3 := dbms_utility.get_time;

24

25. 25  dbms_output.put_line('Execution Time (secs)');

26. 26  dbms_output.put_line('---------------------');

27. 27  dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));

28. 28  dbms_output.put_line('FORALL: '||TO_CHAR(t3-t2));

29. 29  END;

SQL> /
Execution Time (secs)---------------------
FOR loop: 2592
FORALL: 358
PL/SQL procedure successfully completed

从而可以看出 FORALL语句在性能上有显著提高。
注释:SQL语句能涉及多个集合,然而,性能提高只适用于下标集合(subscripted collections)