参考Kyte的《Oracle专家高级编程》第11章。
环境:Oracle 11g2(memory_target=1808M),PL/SQL Developer 9.0。
测试内容是在一个只有1列的空表中插入100000行,比较速度,单用户模式,分有无主键两种情况。
create table t_num (num_id number);
alter table t_num add constraint pk_t_num primary key (num_id);
1. 硬解析
declare
i integer;
begin
for i in 1 .. 100000 loop
execute immediate
'insert into t_num values ('||i||')';
end loop;
commit;
end;
结果:24.944s(无主键)
,26.754
(有主键)
注:取第二次跑的结果,第一次先预热shared mem,不过结果发现两次时间差异不大。
truncate table t_num; --每次做完记得truncate,以后不再重复
2. 软解析
declare
i integer;
begin
for i in 1 .. 100000 loop
execute immediate
'insert into t_num values (:v1)'
using i;
end loop;
commit;
end;
结果:2.402s
(无主键)
,3.744
(有主键)
3. 软解析(静态sql)
declare
i integer;
begin
for i in 1 .. 100000 loop
insert into t_num values (i);
end loop;
commit;
end;
结果:2.153s
(无主键)
,3.026
(有主键)
注:的确比2中要好一些,10%速度提升,似乎没有想象中的那么大
4. bulk collect
declare
i integer;
TYPE t_typ IS TABLE OF number;
v_p t_typ := t_typ();
begin
for i in 1 .. 100000 loop -- 初始化collection
v_p.extend;
v_p(i) := i;
end loop;
forall i in 1 .. v_p.count -- 不能写成
forall i in 1 .. 100000
insert into t_num(num_id) values (v_p(i));
commit;
end;
结果:0.078s
(无主键),0.327
(有主键)
注:bulk collect真是快啊,只是代码上比较麻烦。1.forall后面只能跟一条简单的dml,复杂的语句都不能用,不过这个也符合forall的设计精神;2.forall只针对collection,所以只好之前先新建一个type并初始化。
从上面的结果看来,的确用bulk collect会有数量级的性能提升,开发或者导数据的时候,要尽量使用bulk collect的方式。不过这个过程中药考虑到redo和undo的大小,虽然insert的log量比较少,应该一般不会超过大小限制。
接下去,再补一个关于使用bulk collect来load数据的测试。
将刚才的t_num表中插入1000000条数据(是刚才的实验的数据量的10倍),从1~1000000,然后将这些数据load到plsql中的一个collection里面。
1. 逐个fetch
declare
i integer;
TYPE t_typ IS TABLE OF number;
v_p t_typ := t_typ();
cursor cur_t_num is
select * from t_num;
begin
for rec_t_num in cur_t_num loop
v_p.extend;
v_p(v_p.last) :=
rec_t_num.num_id;
end loop;
dbms_output.put_line(v_p.count);
end;
结果:0.858s
(无主键),0.889s
(有主键)
2. bulk collect
declare
i integer;
TYPE t_typ IS TABLE OF number;
v_p t_typ := t_typ();
cursor cur_t_num is
select * from t_num;
begin
select * bulk collect into v_p
from t_num;
dbms_output.put_line(v_p.count);
end;
结果:0.219
(无主键),0.25s
(有主键)
两个方法相比来看,bulk collect方法时间消耗大约是另一种的1/4,再一次证明了bulk collect在性能上的提升是很明显的。不过比较奇怪的一点是,为什么有主键情况的
select * from t_num这句的执行要比没有主键的时候要慢一些?执行计划里面看起来是一样的,都是TABLE ACCESS FULL,这个需要后续进一步分析。
最后的最后,还有一个一条sql搞定1~100000条数据插入的方法:
insert into
t_num
select rownum from dual
connect by level <= 100000;
结果:0.093s
(无主键),0.328
(有主键)
性能上和bulk collect的差不多,小数位数的差异应该是误差造成的。