create table test_table
(id varchar2(12)
) pctfree 10 pctused 75 nologging;
-- 使用 FOR LOOP 寫入資料
set timi on
begin
for i in 1..1000000 loop
insert into test_table(id)
values('test'||lpad(i,8,0));
end loop;
commit;
end;
/
目前歷時: 00:00:44.07 (需時 44秒)
set timi off
truncate table test_table;
set timi on
declare
type num_table_type is table of number(7)
index by binary_integer;
num num_table_type;
begin
for i in 1..1000000 loop
num(i):=i;
end loop;
forall i in num.FIRST .. num.LAST
insert into test_table(id)
values('test'||lpad(num(i),8,0));
commit;
end;
/
目前歷時: 00:00:05.09 (僅需時 5秒)
set timi off
drop table test_table;
結論:
將100萬筆資料寫入一個table時, FORALL 效能上遠大優於 FOR LOOP ,可節省39秒
留言列表