Oracle 逐条和批量插入数据方式对比

[img]http://dl2.iteye.com/upload/attachment/0096/7623/c85589a6-c7ea-3b80-93fd-e3b47e742df4.jpg[/img]
创建测试表

create table base_users

(

userid varchar2(16),

username varchar2(32),

passwd varchar2(16)

)tablespace cat_data;

采用一条一条插入的方式

create or replace procedure insert_data_one_by_one(n in number)

as

currentId number(16) := 0;

l_userid varchar2(16);

l_username varchar2(32);


sqltext varchar2(256);


begin
dbms_output.put_line('begin ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

sqltext := 'insert into base_users(userid, username, passwd) values(:userid, :username,' || '111111 )';


loop
currentId:=currentId + 1;
l_userid:= to_char(currentId);
l_username:= to_char(18600000000 + currentId);

execute immediate sqltext using l_userid, l_username;
exit when currentId >= n;
end loop;
commit;
dbms_output.put_line('end commit ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));
end insert_data_one_by_one;
/

采用批量插入的方式

create or replace procedure insert_data_bulk(n in number)

as

i int;

tmp_userid number;

tmp_username number;

type useridArray is table of varchar2(16) index by binary_integer;

type usernameArray is table of varchar2(32) index by binary_integer;

puserid useridArray;

pusername usernameArray;

begin

dbms_output.put_line('begin ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

tmp_userid := 1;

tmp_username := 18600000000;

for i in 1 .. n loop

puserid(i) := tmp_userid;

pusername(i) := tmp_username;

tmp_userid := tmp_userid + 1;

tmp_username := tmp_username + 1;

end loop;

forall i in 1 ..n

insert into base_users(userid, username, passwd)

values(puserid(i), pusername(i), '111111');

commit;

dbms_output.put_line('end ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

endinsert_data_bulk;

/

测试1千万条数据的插入

SQL>set serveroutput on

SQL>begin

insert_data_one_by_one(10000000);

end;

/

begin ...22:14:01.572928000
end commit ...22:20:43.911104000





SQL>truncate table base_users;




SQL>begin

insert_data_bulk(10000000);

end;

/

begin ...22:25:31.497810000
end ...22:27:23.801515000