bulk forall 的測試(轉)

bulk forall 的測試

SQL> select count(*) from limit_tmp;

COUNT(*)
----------
700750

create table limit_bak as select CUST_CODE,PROD_CODE,DAY_LIMIT from limit_tmp where 1=2;

create table limit_bak1 as select CUST_CODE,PROD_CODE,DAY_LIMIT from limit_tmp where 1=2;

create table limit_bak2 as select CUST_CODE,PROD_CODE,DAY_LIMIT from limit_tmp where 1=2;

用一般的方式插入
begin
for c1 in (select cust_code,prod_code,day_limit from limit_tmp) loop
insert into limit_bak2 values (c1.cust_code,c1.prod_code,c1.day_limit);
end loop;
commit;
end;

PL/SQL procedure successfully completed

Executed in 42.515 seconds

用 bulk collect + forall 的方式插入資料
declare
TYPE custtab IS TABLE OF varchar2(12)
INDEX BY BINARY_INTEGER;
TYPE prodtab IS TABLE OF VARCHAR2(8)
INDEX BY BINARY_INTEGER;
TYPE limittab IS TABLE OF NUMBER(12,2)
INDEX BY BINARY_INTEGER;

custarr custtab;
prodarr prodtab;
limitarr limittab;
begin
select cust_code,prod_code,day_limit bulk collect into custarr,prodarr,limitarr
from limit_tmp;
forall i in 1 .. custarr.count
insert into limit_bak1 values (custarr(i),prodarr(i),limitarr(i));
commit;
end;

PL/SQL procedure successfully completed

Executed in 6.515 seconds

直接語句插入
insert into limit_bak select cust_code,prod_code,day_limit from limit_tmp;

700750 rows inserted

Executed in 3.125 seconds

可見還是通過語句直接插入的最快，但是在需要在程式中批量插入的時候，bulk 和forall的確是可以提高速度的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242162/，如需轉載，請註明出處，否則將追究法律責任。