今天项目中要做性能测试,需要在oracle中一个表造5000万的数据,总结如下:
1. 最好10000次提交一次
2. 注意表空间的大小
3. 面对主键唯一的情况,最好用一个单独的计数器,直接用i*j会有重复,插入不成功
create
or
replace
procedure sp_proc1
as
v_num number:=1;
begin
loop
insert into t_user values(v_num, '李四'||v_num);
exit when v_num=1000000;
if mod(v_num, 10000)=0 then
commit;
end if;
v_num:=v_num+1;
end loop;
end;
/
v_num number:=1;
begin
loop
insert into t_user values(v_num, '李四'||v_num);
exit when v_num=1000000;
if mod(v_num, 10000)=0 then
commit;
end if;
v_num:=v_num+1;
end loop;
end;
/
declare
i number := 1;
j number := 1;
count number := 1002275000; //基数, 一般都找现存表中最大值
begin
for i in 1..10000
loop
for j in 1..5000
loop
count := count +1;
insert into product(ID, GMT_CREATE, MT_MODIFIED, OMPANY_ID, ) values( count, to_timestamp(sysdate, 'DD-MON-RR HH.MI.SS.FF AM'), o_timestamp(sysdate, 'DD-MON-RR HH.MI.SS.FF AM'),i+10000 );
end loop;
commit;
end loop;
end;
i number := 1;
j number := 1;
count number := 1002275000; //基数, 一般都找现存表中最大值
begin
for i in 1..10000
loop
for j in 1..5000
loop
count := count +1;
insert into product(ID, GMT_CREATE, MT_MODIFIED, OMPANY_ID, ) values( count, to_timestamp(sysdate, 'DD-MON-RR HH.MI.SS.FF AM'), o_timestamp(sysdate, 'DD-MON-RR HH.MI.SS.FF AM'),i+10000 );
end loop;
commit;
end loop;
end;
转载于:https://blog.51cto.com/tianya23/240936