I have table with 58 million records with having 30 columns in which I want update one column by mapping with another table which is having 45 million records with two columns.
MERGE INTO /*+ PARALLEL(tbl_temp) */ tbl_temp tcm
USING (SELECT frn.id, frn.risk FROM temp_new frn ) a
ON (a.id = tcm.fi_id) WHEN MATCHED THEN UPDATE
SET tcm.risk_label=a.risk;
above is the query which I have tried but it is taking more than 12 hrs. I have tried with batch wise update also with batch size 10000 but same issue. Is there any other way to improve the performance of the query
if you have enough free space use recreate your table - create new table with temp name and same structure, use insert as select from current table left joined to mapping table, using parallel and append hint. Rename current table, rename new table to actual name. Move all indexes/synonyms, grants/triggers and etc from old table to the new one. Test your application. If it's fine then you can drop the old one.