Oracle Bulk Binding 批量绑定

郑全

批量绑定(Bulk Binding)通过一次绑定一个完整的集合，然后在PL/SQL块内部按照数组进行数据处理来加快SQL语句的效率。
通过两个DML语句：Bulk Collect和ForAll来实现。
Bulk Collect—用来提高查询(select)的性能
ForAll—用来提高(Insert,Delete,Update)的性能。
测试环境：
软件环境 linux 6.4 + ORACLE10g
硬件环境 虚拟机 1c + RAM 1500M

步骤一.创建一个测试表
create table T3 (pid number(20),pname varchar2(50));
步骤二.我们先测一下(10w笔) insert操作
1．Loop循环
declare
type IDTab is table of number(20) index by binary_integer;
type NameTab is table of varchar2(50) index by binary_integer;
pid IDTab;
pname NameTab;
begin
      for j in 1..100000 loop
          pid(j) := j;
          pname(j) := 'Name is ' || to_char(j);
      end loop;
    
      for i in 1..100000 loop
          insert into T3 values(pid(i),pname(i));
      end loop;
end;
--耗时6.391s
2.使用FORALL
declare
type IDTab is table of number(20) index by binary_integer;
type NameTab is table of varchar2(50) index by binary_integer;
pid IDTab;
pname NameTab;
begin
      for j in 1..100000 loop
          pid(j) := j;
          pname(j) := 'Name is ' || to_char(j);
      end loop;
    
      forall i in 1..100000
           insert into T3 values(pid(i),pname(i));
end;
--耗时0.719s
性能差别太明显了~~
步骤三.我们再测一下(100w笔) insert操作
1．Loop循环
--耗时79.532s
2.使用FORALL
--耗时274.056s
我们发现使用FORALL反而会比LOOP循环慢
3.使用FORALL 测50w
--耗时 10.322s
4.使用FORALL 测80w
--耗时 30.901s
5.使用FORALL 测80w
--耗时 90.333s
现在不难理解了，由于批量执行数据的大小也会对结果产生重大影响，数组太大，占用缓存空间，增加了额外的开销，本人的测试环境较弱，问题就反映了出来。
步骤四.解决方法 – 分批操作，每次10w笔，共10次
declare
type IDTab is table of number(20) index by binary_integer;
type NameTab is table of varchar2(50) index by binary_integer;
pid IDTab;
pname NameTab;
n_count number;
begin
      for j in 1..1000000 loop
          pid(j) := j;
          pname(j) := 'Name is ' || to_char(j);
      end loop;
    
      n_count := 100000;
      loop
      exit when n_count > 1000000;
         
      forall i in (n_count - 100000 + 1)..n_count
           insert into T3 values(pid(i),pname(i));
    
      n_count := n_count + 100000;
      end loop;
end;
--耗时10.045s
--分批操作，每次5w笔， 耗时7.609s
--分批操作，每次1w笔， 耗时6.412s
--分批操作，每次1k笔， 耗时6.5s
可见不是我们需要经过多次反复的测试才能得到一个合适的基数。
*Bulk Collect 的使用方法
1．SELECT INTO中使用
set serveroutput on;
declare
type IDTab is table of T3.PID%type;
type NameTab is table of T3.PNAME%type;
pid IDTab;
pname NameTab;
begin
select pid,pname bulk collect into pid,pname from T3;
for i in pid.first..1000 loop
     dbms_output.put_line(pid(i)||'-'||pname(i));
end loop;
end;
/
2．FETCH INTO中使用
declare
type IDTab is table of T3.PID%type;
type NameTab is table of T3.PNAME%type;
pids IDTab;
pnames NameTab;
cursor c1 is select pid,pname from T3;
begin
open c1;
fetch c1 bulk collect into pids,pnames;
for i in pids.first..1000 loop
     dbms_output.put_line(pids(i)||'-'||pnames(i));
end loop;
close c1;
end;
/