oracle insert大批量效率,oracle大批量插入数据效率问题

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);beginfor i

in 1..5000 loop

id_table(i):=i;

name_table(i) :=

'name'||i;end loop;

start_time :=dbms_utility.get_time;for i in

1..id_table.count

loopinsert 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));for i

in 5001..10000 loop

id_table(i):=i;

name_table(i) :=

'name'||i;end loop;

start_time :=dbms_utility.get_time;

forall i in 5001..id_table.countinsert into demo values(id_table(i),name_table(i));

end_time := dbms_utility.get_time;

dbms_output.put_line('使用批量插入数据总计时间:'||to_char((end_time-start_time)/100));for i

in 5001..10000 loop

id_table(i):=i;

name_table(i) :=

'N'||i;end loop;

start_time :=dbms_utility.get_time;

forall i in 5001..id_table.countupdate demo set name=name_table(i) where id =id_table(i);

end_time := dbms_utility.get_time;

dbms_output.put_line('使用批量更新数据总计时间:'||to_char((end_time-start_time)/100));for i

in 1..10000 loop

id_table(i):=i;end

loop;

start_time :=dbms_utility.get_time;

forall i in 1..id_table.countDELETE FROM demo where id=id_table(i);

end_time := dbms_utility.get_time;

dbms_output.put_line('使用批量删除数据总计时间:'||to_char((end_time-start_time)/100));end;

运行结果: 不使用批量插入总计时间:.96使用批量插入总计时间:.04使用批量更新总计时间:.64使用批量删除总计时间:2.98