Bulk绑定是如何提高性能的。有空再翻译吧。(from oracle)

1587人阅读 评论(0) 收藏 举报

How Do Bulk Binds Improve Performance?

The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:

  • 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 When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.

A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.

To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement.

To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

For full details of the syntax and restrictions for these statements, see "FORALL Statement" and "SELECT INTO Statement".

Example: Performing a Bulk Bind with DELETE

The following DELETE statement is sent to the SQL engine just once, even though it performs three DELETE operations:

DECLARE
   TYPE NumList IS VARRAY(20) OF NUMBER;
   depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
   FORALL i IN depts.FIRST..depts.LAST
      DELETE FROM emp WHERE deptno = depts(i);
END;

Example: Performing a Bulk Bind with INSERT

In the example below, 5000 part numbers and names are loaded into index-by tables. All table elements are inserted into a database table twice: first using a FOR loop, then using a FORALL statement. The FORALL version is much faster.

SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

Table created.

SQL> GET test.sql
 1  DECLARE
 2     TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
 3     TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
 4     pnums  NumTab;
 5     pnames NameTab;
 6     t1 NUMBER(5);
 7     t2 NUMBER(5);
 8     t3 NUMBER(5);
 9     
10     
11  BEGIN
12     FOR j IN 1..5000 LOOP  -- load index-by tables
13        pnums(j) := j;
14        pnames(j) := 'Part No. ' || TO_CHAR(j); 
15     END LOOP;
16     t1 := dbms_utility.get_time;
17     FOR i IN 1..5000 LOOP  -- use FOR loop
18        INSERT INTO parts VALUES (pnums(i), pnames(i));
19     END LOOP;
20     t2 := dbms_utility.get_time;
21     FORALL i IN 1..5000  -- use FORALL statement
22        INSERT INTO parts VALUES (pnums(i), pnames(i));
23     get_time(t3);
24     dbms_output.put_line('Execution Time (secs)');
25     dbms_output.put_line('---------------------');
26     dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
27     dbms_output.put_line('FORALL:   ' || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 32
FORALL:   3

PL/SQL procedure successfully completed.

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1710990次
    • 积分:20432
    • 等级:
    • 排名:第401名
    • 原创:492篇
    • 转载:112篇
    • 译文:19篇
    • 评论:217条
    文章存档
    最新评论
    老婆