Oracle Bulk Binding 批量绑定 oracle批量执行脚本

批量绑定(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;

/