从Oracle8开始,oracle为PL/SQL引入了两个新的数据操纵语言(DML)语句:BULK COLLECT和FORALL。这两个语句在PL/SQL内部进行一种数组处理;BULK COLLECT提供对数据的高速检索,FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。Oracle数据库使用这些语句大大减少了PL/SQL与SQL语句执行引擎的环境切换次数,从而使其性能有了显著提高。
现在就用实践证明for..loop循环语句和forall在执行性能上的区别
例子中使用到的type ... is table of ....这里先不做解释,以后讲BULK COLLECT会详细提到
1.创建空表
create table test(sid int,uname varchar2(20));
2.创建过程计算时间
create or replace procedure get_time(count_time out number)
is
begin
select to_char(sysdate,'mmmmmmm') into count_time from dual;
end;
3.创建过程分别用FOR.. LOOP语句和FORALL向表中插入100万条记录
create or replace procedure time_of_for_and_forall
is
type sid_type is table of fw.test.sid%type index by pls_integer;
sid_var sid_type;
type uname_type is table of fw.test.uname%type index by pls_integer;
uname_var uname_type;
count_time1 char(10);
count_time2 char(10);
count_time3 char(10);
begin
for i in 1..1000000 loop
sid_var(i) := i;
uname_var(i) := to_char(i);
end loop;
fw.get_time(count_time1);
for j in 1..1000000 loop
insert into fw.test values(sid_var(j),uname_var(j));
end loop;
commit;
fw.get_time(count_time2);
forall k in 1..1000000
insert into fw.test values(sid_var(k),uname_var(k));
commit;
fw.get_time(count_time3);
dbms_output.put_line('使用for循环插入100万条数据所花的时间为'
||to_number(count_time2-count_time1));
dbms_output.put_line('使用forall循环插入100万条数据所花的时间为'
||to_number(count_time3-count_time2));
end;
3.然后调用上面的过程
declare
begin
fw.time_of_for_and_forall;
end;
在我的机器上输出的结果是
使用for循环插入100万条数据所花的时间为201
使用forall循环插入100万条数据所花的时间为21
同样你也可以直接在sql*plus里直接比较2个匿名块的执行时间
set timing on
使用for循环
declare
type sid_type is table of fw.test1.sid%type index by pls_integer;
sid_var sid_type;
type uname_type is table of fw.test1.uname%type index by pls_integer;
uname_var uname_type;
begin
for i in 1..1000000 loop
sid_var(i) := i;
uname_var(i) := to_char(i);
end loop;
for k in 1..1000000 loop
insert into fw.test2 values(sid_var(k),uname_var(k));
end loop;
commit;
end;
/
使用forall循环
declare
type sid_type is table of fw.test1.sid%type index by pls_integer;
sid_var sid_type;
type uname_type is table of fw.test1.uname%type index by pls_integer;
uname_var uname_type;
begin
for i in 1..1000000 loop
sid_var(i) := i;
uname_var(i) := to_char(i);
end loop;
forall k in 1..1000000
insert into fw.test2 values(sid_var(k),uname_var(k));
commit;
end;
/
在SQL*PLUS里输出的时间结果是
使用forall: 00: 00: 31.04
使用for: 00: 03: 28.08
现在就用实践证明for..loop循环语句和forall在执行性能上的区别
例子中使用到的type ... is table of ....这里先不做解释,以后讲BULK COLLECT会详细提到
1.创建空表
create table test(sid int,uname varchar2(20));
2.创建过程计算时间
create or replace procedure get_time(count_time out number)
is
begin
select to_char(sysdate,'mmmmmmm') into count_time from dual;
end;
3.创建过程分别用FOR.. LOOP语句和FORALL向表中插入100万条记录
create or replace procedure time_of_for_and_forall
is
type sid_type is table of fw.test.sid%type index by pls_integer;
sid_var sid_type;
type uname_type is table of fw.test.uname%type index by pls_integer;
uname_var uname_type;
count_time1 char(10);
count_time2 char(10);
count_time3 char(10);
begin
for i in 1..1000000 loop
sid_var(i) := i;
uname_var(i) := to_char(i);
end loop;
fw.get_time(count_time1);
for j in 1..1000000 loop
insert into fw.test values(sid_var(j),uname_var(j));
end loop;
commit;
fw.get_time(count_time2);
forall k in 1..1000000
insert into fw.test values(sid_var(k),uname_var(k));
commit;
fw.get_time(count_time3);
dbms_output.put_line('使用for循环插入100万条数据所花的时间为'
||to_number(count_time2-count_time1));
dbms_output.put_line('使用forall循环插入100万条数据所花的时间为'
||to_number(count_time3-count_time2));
end;
3.然后调用上面的过程
declare
begin
fw.time_of_for_and_forall;
end;
在我的机器上输出的结果是
使用for循环插入100万条数据所花的时间为201
使用forall循环插入100万条数据所花的时间为21
同样你也可以直接在sql*plus里直接比较2个匿名块的执行时间
set timing on
使用for循环
declare
type sid_type is table of fw.test1.sid%type index by pls_integer;
sid_var sid_type;
type uname_type is table of fw.test1.uname%type index by pls_integer;
uname_var uname_type;
begin
for i in 1..1000000 loop
sid_var(i) := i;
uname_var(i) := to_char(i);
end loop;
for k in 1..1000000 loop
insert into fw.test2 values(sid_var(k),uname_var(k));
end loop;
commit;
end;
/
使用forall循环
declare
type sid_type is table of fw.test1.sid%type index by pls_integer;
sid_var sid_type;
type uname_type is table of fw.test1.uname%type index by pls_integer;
uname_var uname_type;
begin
for i in 1..1000000 loop
sid_var(i) := i;
uname_var(i) := to_char(i);
end loop;
forall k in 1..1000000
insert into fw.test2 values(sid_var(k),uname_var(k));
commit;
end;
/
在SQL*PLUS里输出的时间结果是
使用forall: 00: 00: 31.04
使用for: 00: 03: 28.08