Forum Stats

  • 3,875,416 Users
  • 2,266,914 Discussions
  • 7,912,203 Comments

Discussions

PLSQL job for loading 20 million records from an external table taking around 1 hour run time in or

Sunny86
Sunny86 Member Posts: 265 Blue Ribbon
edited Nov 9, 2022 2:39PM in SQL & PL/SQL

 Hello experts,

 I am a naive oracle plsql programmer,we are trying to load around 20 Million records from an external table.

 externaltable->intermediate table -> final table.

 We have few update statements also in place. It is taking around 1 hour. Is it possible to reduce the run time. Any advise is highly appreciated. Program added below.

procedure Customers_Load is
tgt_count number;
src_count number;
begin
execute immediate 'Truncate table customers_stg';
--external table to stage table load
begin
insert into customers_stg(Area_CODE, Code, CLASS, EFFE_DATE, STATUS, NAT, SEG_CODE, 
M_CODE, M_NAME, M_CODE2) (select Area_CODE, Code, CLASS, EFFE_DATE, STATUS, NAT, SEG_CODE, 
M_CODE, M_NAME, M_CODE2 from cust_external );
commit;
end;


-------Customer Target table count comparing against Stage count. If stage has more records then it will load. otherwise skip.
begin
select sum(tgt_count),sum(src_count) into tgt_count,src_count
from
(
 select count(*)tgt_count,0 src_count
 from cust_target Tgt
 union all 
select  0,count(*)src_count from
customers_stg Src where CIC<>'CIC'
);
insert into daily_data_refresh(Proc_Name,Proc_Desc,Refresh_Date,Refresh_Time)values('Customers_Load','Checking no_of_records tgtcount '||to_char(tgt_count)||
'- source count ' ||to_char(src_count),trunc(sysdate),sysdate);


end;
--------------check if new clients added


if tgt_count<src_count
then
begin
execute immediate 'Truncate table cust_target';
insert into cust_target(CIC,Nat,Code,code1,Code2,status,Eff_date,Code3,Cust_Name,loaddate,Seg_Class)
(select trim(leading '0' from CIC),to_number(trim(Nat))
,RPAD(trim(leading '0' from Code),5,'0')
,trim(code1)
,trim(Code2),trim(status),
to_Date(substr(trim(Eff_date),1,10),'MM/DD/YYYY '),
Lpad(trim(Code3),4,'0'),trim(Cust_Name),
trunc(sysdate),trim(Segment_Code)||NVL(Lpad(trim(Class_Code),4,'0'),'0000')
from customers_stg 
commit;
end;
 insert into daily_data_refresh(Proc_Name,Proc_Desc,Refresh_Date,Refresh_Time)values('Customers_Load','Before update--Customers Loaded to Final Table',trunc(sysdate),sysdate);


----------------update Customer codedet and codename
begin
 update cust_target c set(codedet,codename)=(select codedet,codename from codeset b where c.code1=b.code1);-----where rownum<100000------remove condition
 commit;
 end;
 insert into daily_data_refresh(Proc_Name,Proc_Desc,Refresh_Date,Refresh_Time)values('Customers_Load','codedet,codename Updated',trunc(sysdate),sysdate);
 -----------update Customer  seg
 
 begin
 update cust_target tgt set(code0,code1,code2,code3,code4)=(select max(code0),max(code1),
 max(code2),max(code3),max(code4)
from
(
select code0,Null code1,Null code2,Null code3,Null code4 from cust_seg cs where cs.seg_Code=tgt.code0
union all
select NULL code0,code1,Null code2,Null code3,Null code4 from cust_seg cs where cs.seg_code=tgt.code1
union all
select NULL code0,Null code1,code2,Null code3,Null code4 from cust_seg cs where cs.seg_code=tgt.code2
union all
select NULL code0,Null code1,Null code2, code3,Null code4 from cust_seg cs where cs.seg_code=tgt.code3
union all
select NULL code0,Null code1,Null code2,Null code3, code4 from cust_code4 cn where cn.code4=tgt.code4
));------where rownum<100000-----remove condition
 commit; 
  insert into daily_data_refresh(Proc_Name,Proc_Desc,Refresh_Date,Refresh_Time)values('Customers_Load','code0 to 4 Updated',trunc(sysdate),sysdate);


 end;
end if;
insert into daily_data_refresh(Proc_Name,Proc_Desc,Refresh_Date,Refresh_Time)values('Customers_Load','Customer Load Completed',trunc(sysdate),sysdate);


end; 


Tagged:
«1

Answers