Hi All,
i want to insert around 2 millions of data from source to target. and target table is on remote Db. I have written below code which taking long time (approx 6 hrs) to complete:
declare
p_table_name VARCHAR2(100) := 'IGCMS_ENVISION_CUST_affl_mstr';
v_input VARCHAR2(4000) DEFAULT 'CDW.ENVISION_CUST_AFFL_MSTR';
v_tgt VARCHAR2(100);
v_tgt1 VARCHAR2(100) ;
P_tgt1 VARCHAR2(100) := 'IGCMSM1';
v_tgt_dblink VARCHAR2(100);
begin
v_tgt_dblink := 'IGCMSM1_DBLINK';
v_tgt := p_tgt1||'.'||p_table_name||'@'||v_tgt_dblink;
v_tgt1 := p_tgt1||'.'||p_table_name;
--Truncate and insert temporary table with stg data
EXECUTE IMMEDIATE 'BEGIN dbms_utility.exec_ddl_statement@'||v_tgt_dblink||' (''TRUNCATE TABLE '||v_tgt1||''');
END;';
EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO '|| v_tgt||' SELECT (SELECT coalesce(max(Seq), 0) from '||v_tgt||') + ROWNUM as SEQ,A.*
FROM (SELECT DISTINCT AFFILIATION_SK ,
SRC_SK ,
TYPE ,
FRM_CUST ,
FRM_CUST_NAME ,
FRM_CUST_ID ,
TO_CUST_SK ,
TO_CUST_NAME ,
TO_CUST_ID ,
SYSDATE as CREATE_TS,
''user1'' as CREATE_USER,
SYSDATE as UPDATE_TS,
''user1'' as UPDATE_USER
FROM ' || v_input ||' X )A';
COMMIT;
END;
Could you please suggest any way to improve the performance.
Thanks in advance.