Oracle PLSQL long run issue

Question

there is long run issue with below code kindly help to rectify this issue. Thanks in advance.

Declare
l_status varchar2(10);

Begin

Insert into @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
  Select ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD 
           from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}});

Commit;

For i in ( Select mfu_source.Customer_Surrogate_ID CUST_SROGT_ID, Agg.TRD_REF_NUM TRD_REF_NUM, mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
          from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG,
          {{sourceTableLatest('mfu_table')}} partition ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source
         Where (mfu_source.TA_LINK_ID=AGG.TRD_REF_NUM or mfu_source.TRANSACTION_ID=AGG.TRD_REF_NUM)
         and trim(AGG.ENTRP_CUST_ID_NUM) is null) 
         Loop
         
         
    Update @table_name set ENTRP_CUST_ID_NUM= i.ENTRP_CUST_ID_NUM where TRD_REF_NUM=i.TRD_REF_NUM;
    
    Commit;
End Loop;

END;

the above code take long run issue need to rectify the issue with simple and easy.

Answer 1

You can use a single MERGE statement (rather than multiple statements in a loop):

Declare
  l_status varchar2(10);
Begin
  Insert into @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
  Select ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD 
  from   {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}});

  MERGE INTO @table_name dst
  USING (
    Select mfu_source.Customer_Surrogate_ID CUST_SROGT_ID,
           Agg.TRD_REF_NUM TRD_REF_NUM,
           mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
    from   {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
           INNER JOIN {{sourceTableLatest('mfu_table')}} partition ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source
           ON    mfu_source.TA_LINK_ID     = AGG.TRD_REF_NUM
              or mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
    Where  trim(AGG.ENTRP_CUST_ID_NUM) is null
  ) src
  ON dst.TRD_REF_NUM = src.TRD_REF_NUM
  WHEN MATCHED THEN
    UPDATE
    SET dst.ENTRP_CUST_ID_NUM = src.ENTRP_CUST_ID_NUM;

  Commit;
END;
/