写了这样一个数据搬迁脚本,测试了一下,结果一百多万数据跑了将近五个小时,不知道怎么优化,贴出来,求各位大神指导如何优化:
declare
-- Local variables here
i integer;
minTaskNo number;
maxTaskNo number;
taskID number;
custID varchar2(32);
v_err varchar2(255); TYPE taskInfo is RECORD(
taskIDs NTL_ASSIGNED_TASK_FREEINS.ASSIGNED_TASK_ID%type); TYPE t_task_info IS TABLE of taskInfo;
v_taskIDs t_task_info;
cursor taskCur is
select NTF.assigned_task_id
from NTL_ASSIGNED_TASK_FREEINS NTF
where NTF.ASSIGNED_TASK_ID >= minTaskNo and NTF.ASSIGNED_TASK_ID <= maxTaskNo;
begin
-- Test statements here
select min(t.assigned_task_id)
into minTaskNo
from ntl_assigned_task_freeins t;
select max(t.assigned_task_id)
into maxTaskNo
from ntl_assigned_task_freeins t;
open taskCur;
loop
fetch taskCur bulk collect
into v_taskIDs limit 50000;
begin
for IDs in 1 .. v_taskIDs.count loop
taskID := v_taskIDs(IDs).taskIDs;
select c.its_cust_id
into custID
from ntl_task_customer c
where c.ASSIGNED_TASK_ID = taskID;
--dbms_output.put_line(taskID || ' ' || custID);
insert into NTL_TASK_CUSTOMER_FREEINS
select * from NTL_TASK_CUSTOMER where ASSIGNED_TASK_ID = taskID;
insert into ntl_cust_modify_his_freeins
select * from NTL_CUSTOMER_MODIFY_HISTORY where ITS_CUST_ID = custID;
insert into NTL_TASK_CUSTOMER_SURPLUS_FREE
select * from NTL_TASK_CUSTOMER_SURPLUS_INFO where ITS_CUST_ID = custID;
insert into NTL_ADDRESS_FREEINS
select * from NTL_ADDRESS where ITS_CUST_ID = custID;
insert into NTL_TELEPHONE_FREEINS
select * from NTL_TELEPHONE where ITS_CUST_ID = custID;
delete from NTL_TELEPHONE where ITS_CUST_ID = custID;
delete from NTL_ADDRESS where ITS_CUST_ID = custID;
delete from NTL_TASK_CUSTOMER_SURPLUS_INFO where ITS_CUST_ID = custID;
delete from NTL_CUSTOMER_MODIFY_HISTORY where ITS_CUST_ID = custID;
delete from NTL_TASK_CUSTOMER where ASSIGNED_TASK_ID = taskID;
end loop;
EXCEPTION
when others then
v_err := SQLERRM;
dbms_output.put_line('' || v_err);
rollback;
end;
commit;
exit when taskCur% notfound;
end loop;
close taskCur;
end;oracle
declare
-- Local variables here
i integer;
minTaskNo number;
maxTaskNo number;
taskID number;
custID varchar2(32);
v_err varchar2(255); TYPE taskInfo is RECORD(
taskIDs NTL_ASSIGNED_TASK_FREEINS.ASSIGNED_TASK_ID%type); TYPE t_task_info IS TABLE of taskInfo;
v_taskIDs t_task_info;
cursor taskCur is
select NTF.assigned_task_id
from NTL_ASSIGNED_TASK_FREEINS NTF
where NTF.ASSIGNED_TASK_ID >= minTaskNo and NTF.ASSIGNED_TASK_ID <= maxTaskNo;
begin
-- Test statements here
select min(t.assigned_task_id)
into minTaskNo
from ntl_assigned_task_freeins t;
select max(t.assigned_task_id)
into maxTaskNo
from ntl_assigned_task_freeins t;
open taskCur;
loop
fetch taskCur bulk collect
into v_taskIDs limit 50000;
begin
for IDs in 1 .. v_taskIDs.count loop
taskID := v_taskIDs(IDs).taskIDs;
select c.its_cust_id
into custID
from ntl_task_customer c
where c.ASSIGNED_TASK_ID = taskID;
--dbms_output.put_line(taskID || ' ' || custID);
insert into NTL_TASK_CUSTOMER_FREEINS
select * from NTL_TASK_CUSTOMER where ASSIGNED_TASK_ID = taskID;
insert into ntl_cust_modify_his_freeins
select * from NTL_CUSTOMER_MODIFY_HISTORY where ITS_CUST_ID = custID;
insert into NTL_TASK_CUSTOMER_SURPLUS_FREE
select * from NTL_TASK_CUSTOMER_SURPLUS_INFO where ITS_CUST_ID = custID;
insert into NTL_ADDRESS_FREEINS
select * from NTL_ADDRESS where ITS_CUST_ID = custID;
insert into NTL_TELEPHONE_FREEINS
select * from NTL_TELEPHONE where ITS_CUST_ID = custID;
delete from NTL_TELEPHONE where ITS_CUST_ID = custID;
delete from NTL_ADDRESS where ITS_CUST_ID = custID;
delete from NTL_TASK_CUSTOMER_SURPLUS_INFO where ITS_CUST_ID = custID;
delete from NTL_CUSTOMER_MODIFY_HISTORY where ITS_CUST_ID = custID;
delete from NTL_TASK_CUSTOMER where ASSIGNED_TASK_ID = taskID;
end loop;
EXCEPTION
when others then
v_err := SQLERRM;
dbms_output.put_line('' || v_err);
rollback;
end;
commit;
exit when taskCur% notfound;
end loop;
close taskCur;
end;oracle
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货