今天看了一个2010年发的一个贴子。记录一下动态使用FORALL语句与FOR的性能对比,测试环境为ORACLE 10G
1.建立二个表
create table a_tab(ver number,id number)
A_tab表中写了20000条记录;
create table b_tab(ver number,id number)
B_tab表中无记录;
2.给a_tab表增加数据;
declare
l_add integer:=0;
begin
for i in 1..20000 loop
l_add:=l_add+1;
execute immediate 'insert into a_tab values(:a,:b)' using i,l_add;
end loop;
commit;
end;
/
PL/SQL procedure successfully completed
Executed in 2.172 seconds
SQL> select count(*) from a_tab;
COUNT(*)
----------
20000
Executed in 0.031 seconds
下面这段代码为不知道表名插入数据,使用FORALL语句实现
declare
v_sql varchar2(4000);
v_tablename varchar2(100):='b_tab';
begin
v_sql:='
declare
type r_outtab is record (ver NUMBER,id NUMBER);
type t_outtab is table of r_outtab index by binary_integer;
v_outtab t_outtab;
v_query varchar2(30000);
begin
v_query :=''select ver,id from a_tab '' ;
execute immediate v_query bulk collect into v_outtab;
forall i in v_outtab.first .. v_outtab.last
insert into '||v_tablename||' values v_outtab(i) ;
end;';
dbms_output.put_line(v_sql);
execute immediate v_sql;
commit;
end;
/
PL/SQL procedure successfully completed
Executed in 0.203 seconds
PL/SQL procedure successfully completed
Executed in 0.125 seconds
以上是二次插入时间
SQL> select count(*) from b_tab;
COUNT(*)
----------
20000
Executed in 0.015 seconds
下面这段代码为FOR语句实现
declare
type r_outtab is record(
ver NUMBER,
id NUMBER);
type t_outtab is table of r_outtab index by binary_integer;
v_outtab t_outtab;
v_tablename varchar2(100);
v_query varchar2(30000);
begin
v_tablename := 'b_tab';
v_query := 'select ver,id from a_tab ';
execute immediate v_query bulk collect into v_outtab;
for i in v_outtab.first .. v_outtab.last loop
execute immediate 'insert into ' || v_tablename || ' values(:a, :b)' using v_outtab(i).ver, v_outtab(i).id;
end loop;
end;
/
PL/SQL procedure successfully completed
Executed in 10.906 seconds
PL/SQL procedure successfully completed
Executed in 27.063 seconds
以上二次插入数据
SQL> select count(*) from b_tab;
COUNT(*)
----------
60000
Executed in 0.078 seconds
通过二次对比,使用FORALL的动态SQL比FOR的插入要快
但是FORALL不支持下面语句请大家注意,使用按上面的例子使用
declare
type r_outtab is record (ver NUMBER,id NUMBER);
type t_outtab is table of r_outtab index by binary_integer;
v_outtab t_outtab;
v_query varchar2(30000);
v_tablename varchar2(100):='a_tab';
begin
v_query :='select ver,id from b_tab ' ;
execute immediate v_query bulk collect into v_outtab;
forall i in v_outtab.first .. v_outtab.last
execute immediate 'insert into '||v_tablename||' values (:a,:b)' using v_outtab(i).ver,v_outtab(i).id ;
end;
ORA-06550: line 13, column 82:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 13, column 11:
PL/SQL: Statement ignored
ORA-06550: line 13, column 11:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL