---可以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个小表,然后并发执行,相当快
sqlldr 插入100万数据很快的。
写个过程,偶一下插了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; / 自己看着改下吧
它是把数据插入到表的后面,使用连续的未使用的块。配合nologging会更快,
从表中插入,可以用楼上两位提供的方法。
另,正常的insert的时候,要注意批量递交,比说说1000条递交一次
我对ora的了解只是停留在基础层。哪位能给段源码呢?不胜感激~~~
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个小表,然后并发执行,相当快
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;
/
自己看着改下吧