Hi Tom,

Need suggestions to improve performance. Following are the methods we tried

1. Using merge

MERGE INTO /*+ PARALLEL(tbl_temp,8) */ tbl_temp tcm
USING (SELECT frn.customer_id, frn.risk FROM temp_new frn ) a
ON (a.id = tcm.fi_id) WHEN MATCHED THEN UPDATE 
SET tcm.risk_label=a.risk;

- Kill the process as it took more than 12 hrs (as nightly build)

2. Tried with batch update with 10000 as batch_size.

- Kill the process as it is also take much time. (by the time we kill the process 44 million records updated)

3. Delete and Insert

created a table new table with records need to update (as we have pending with 14 million records).

begin
  begin
    DELETE /*+ FULL(tbl_temp) PARALLEL (tbl_temp,8) */ from tbl_temp where fi_id IN (SELECT id FROM temp_new );
  commit;
  end;
  begin
    insert /*+ PARALLEL(tbl_temp, 8) */  into tbl_temp
    select * from temp_master;
  commit;
  end;
end;

- Kill the process (same issue as above)

Past one week we are unable find a better solution to update.