写个过程,偶一下插了3000W花了40分钟,100W估计两分钟能搞定:
set serveroutput on
declare
tt constant int:=30000;
i int:=1;
j int:=1;
k int:=1;
n int:=1;
m int:=1;
begin
for i in 1..tt loop
loop
loop
insert into ayss.HistDataT ("DT","STATEID","OBJID","OBJTYPE","VAL")
values(To_char(To_date('2008-08-26 00:00:00','YYYY-MM-DD HH24:MI:SS')+m*(300/(24*60*60))),k,n,0,m);
if n>99 then
n:=1;
exit;
end if;
n:=n+1;
end loop;
if k>9 then
k:=1;
exit;
end if;
k:=k+1;
end loop;
m:=m+1;
end loop;
commit;
end;
/
自己看着改下吧
---可以1000条提交一次
DECLARE
CURSOR C_A IS SELECT * FROM TABLE_A;
TYPE BB IS RECORD(..);
BEGIN
OPEN C_A;
LOOP
FETCH C_A BULK COLLECT
INTO BB LIMIT 1000;
FOR I IN 1 .. BB.COUNT LOOP
INSERT INTO TABLE_B
VALUES(BB.col1,bb.col2,..) ;
END LOOP;
COMMIT;
EXIT WHEN C_A%NOTFOUND;
END LOOP;
END;
--还可以将TABLE_A分区,分为10个或者50个小表,然后并发执行,相当快