Bulk Binds对性能的提高

    当Oracle运行PL/SQL时会使用两套引擎，所有procedural code由PL/SQL engine 完成，所有SQL由SQL engine处理。所以如果Oracle从一个collection中循环执行相同的DML操作，那么为了避免两套engine切换所消耗的系统资源，可以使用bulk binds来把所有的DML操作binding到一次操作中完成。这将极大提高PL/SQL的执行效率。

　以下是简单的测试，用两种方式插入100000条数据，可以看到效率提高了7倍左右。

SQL> CREATE TABLE test1(

    2        id                     NUMBER(10),

    3        description    VARCHAR2(50));

Table created

SQL> ALTER TABLE test1 ADD (

    2        CONSTRAINT test1_pk PRIMARY KEY (id));

Table altered

SQL> SET TIMING ON;

SQL> DECLARE

    2        TYPE id_type                    IS TABLE OF test1.id%TYPE;

    3        TYPE description_type IS TABLE OF test1.description%TYPE;

    4

    5        t_id                     id_type                    := id_type();

    6        t_description    description_type := description_type();

    7    BEGIN

    8        FOR i IN 1 .. 100000 LOOP

    9            t_id.extend;

10            t_description.extend;

11

12            t_id(t_id.last)                                     := i;

13            t_description(t_description.last) := 'Description: ' || To_Char(i);

14        END LOOP;

15

16        FOR i IN t_id.first .. t_id.last LOOP

17            INSERT INTO test1 (id, description)

18            VALUES (t_id(i), t_description(i));

19        END LOOP;

20

21        COMMIT;

22    END;

23    /

PL/SQL procedure successfully completed

Executed in 141.233 seconds

SQL> truncate table test1;

Table truncated

Executed in 0.631 seconds

SQL>

16        FORALL i IN t_id.first .. t_id.last

19

20        COMMIT;

21    END;

22    /

Executed in 27.52 seconds

SQL> select count(*) from test1;

    COUNT(*)

----------

        100000

Executed in 0.04 seconds