一、如何使用批挷定提高性能(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 TYPE NumTab IS TABLE OF parts.pnum%TYPE INDEX BY BINARY_INTEGER;
-
3 TYPE NameTab IS TABLE OF parts.pname%TYPE INDEX BY BINARY_INTEGER;
-
4 pnums NumTab;
-
5 Pnames NameTab;
-
6 t1 NUMBER;
-
7 t2 NUMBER;
-
8 t3 NUMBER;
-
9 BEGIN
-
10 FOR i IN 1..500000 LOOP
-
11 pnums(i) := i;
-
12 pnames(i) := 'Part No.'||to_char(i);
-
13 END LOOP;
-
14 t1 := dbms_utility.get_time;
15
-
16 FOR i IN 1..500000 LOOP
-
17 INSERT INTO parts VALUES(pnums(i),pnames(i));
-
18 END LOOP;
-
19 t2 := dbms_utility.get_time;
20
-
21 FORALL i IN 1..500000
-
22 INSERT INTO parts VALUES(pnums(i),pnames(i));
-
23 t3 := dbms_utility.get_time;
24
-
25 dbms_output.put_line('Execution Time (secs)');
-
26 dbms_output.put_line('---------------------');
-
27 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
-
28 dbms_output.put_line('FORALL: '||TO_CHAR(t3-t2));
-
29 END;
SQL> /
Execution Time (secs)---------------------
FOR loop: 2592
FORALL: 358
PL/SQL procedure successfully completed
从而可以看出 FORALL语句在性能上有显著提高。
注释:SQL语句能涉及多个集合,然而,性能提高只适用于下标集合(subscripted collections)