(1)优化SQL语句;
(2)建议插入数据之前删掉索引,插入后重建索引;
方法1:使用批量拷贝方法
set arraysize 20
set copycommit 5000
copy from scott/biohazard@top a append
using select * from b;
方法2:使用游標,每5000,10000条commit
declare
cursor cur_select is
select time, num from b;
num number := 0;
record_type b%rowtype;
begin
open cur_select;
loop
exit when cur_select%notfound;
fetch cur_select
into record_type.time, record_type.num;
insert into a values (record_type.time, record_type.num);
num := num + 1;
if num = 10000 then
commit;
num := 0;
end if;
end loop;
close cur_select;
commit;
end;
方法三:alter session set sort_area_size=100000000;
insert into tableb select * from tablea;
commit;