批次繫結加快資料處理測試

本文是對批次繫結進行測試，對批次繫結資料處理有一定參加意義，速度提高很是明顯．已完成．

[@more@]

declare
type id_table_type is table of number(6)
index by binary_integer;
type name_table_type is table of varchar2(10)
index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
id_table(i):=i;
name_table(i):='Name'||to_char(i);
end loop;
start_time:=dbms_utility.get_time;
for i in 1..id_table.count loop
insert into demo values(id_table(i),name_table(i));
end loop;
end_time:=dbms_utility.get_time;
dbms_output.put_line('總計時間(秒):'||to_char((end_time-start_time)/100));
end;

SQL> declare
2 type id_table_type is table of number(6)
3 index by binary_integer;
4 type name_table_type is table of varchar2(10)
5 index by binary_integer;
6 id_table id_table_type;
7 name_table name_table_type;
8 start_time number(10);
9 end_time number(10);
10 begin
11 for i in 1..5000 loop
12 id_table(i):=i;
13 name_table(i):='Name'||to_char(i);
14 end loop;
15 start_time:=dbms_utility.get_time;
16 for i in 1..id_table.count loop
17 insert into demo values(id_table(i),name_table(i));
18 end loop;
19 end_time:=dbms_utility.get_time;
20 dbms_output.put_line('總計時間(秒):'||to_char((end_time-start_time)/100));
21 end;
22 /
總計時間(秒):7.67

PL/SQL procedure successfully completed.

下面使用批次繫結:

declare
type id_table_type is table of number(6)
index by binary_integer;
type name_table_type is table of varchar2(10)
index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
id_table(i):=i;
name_table(i):='Name'||to_char(i);
end loop;
start_time:=dbms_utility.get_time;
forall i in 1..id_table.count
insert into demo_bluck values(id_table(i),name_table(i));
end_time:=dbms_utility.get_time;
dbms_output.put_line('總計時間(秒):'||to_char((end_time-start_time)/100));
end;
/

SQL> declare
2 type id_table_type is table of number(6)
3 index by binary_integer;
4 type name_table_type is table of varchar2(10)
5 index by binary_integer;
6 id_table id_table_type;
7 name_table name_table_type;
8 start_time number(10);
9 end_time number(10);
10 begin
11 for i in 1..5000 loop
12 id_table(i):=i;
13 name_table(i):='Name'||to_char(i);
14 end loop;
15 start_time:=dbms_utility.get_time;
16 forall i in 1..5000
17 insert into demo_bluck values(id_table(i),name_table(i));
18 end_time:=dbms_utility.get_time;
19 dbms_output.put_line('總計時間(秒):'||to_char((end_time-start_time)/100));
20 end;
21 /
總計時間(秒):.04

PL/SQL procedure successfully completed.