Oracle——Bulk

需求：向一张表插入1百万的数据，用存储过程实现。

测试：用第一种实现用了30S，如果改为bulk实现，不到2s。

SQL> drop table t purge;

表已删除。

SQL> set timing on
SQL> create table t (x  int);
SQL> begin
      for i in 1.. 1000000
       loop
           execute immediate 'insert into t values(:x)' using i;
       end loop;
       commit;
      end;
    /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 30.24

SQL> truncate table t;

表被截断。

SQL> DECLARE
      TYPE V_X IS table of t%rowtype;
      records V_X;
      cursor cur_query is select rownum x from dual connect by level <=1000000;
    begin
      open cur_query;
      loop
        fetch cur_query bulk collect into records  LIMIT 100000;
          FORALL i in 1 .. records.count
           INSERT INTO T values records(i);
          exit when cur_query%notfound;
      end loop;
      commit;
   end;
   /

已用时间: 00: 00: 01.48

优化原理：

1.Oracle使用2个引擎来执行SQL和代码块：SQL引擎和PL/SQL引擎，SQL语句会导致在两个引擎之间进行context switch，从而影响性能。

2.从本质上讲，使用特殊的block或者subprogram来降低context switches可以提高性能。当SQL语句在loop内使用collection elements作为bind variables来运行时，就会产生大量的context switches。与bulk相关的有2个语法：forall和bulk collect。

FORALL:将数据打包，一次性从PL/SQL引擎发送给SQL引擎。

bulk collect:将处理之后的结果集放到bulk collect里，然后一次性把bulk collect从SQL引擎发送给PL/SQL引擎。

因为bulk减少2个引擎之间的context switches，从而提升了SQL性能，当要处理的数据量越大，使用bulk和不使用bulk性能区别就越明显。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!