Oracle | Update huge table after comparing values with other table

March 2, 2022

I have two huge tables. Let’s call them as ITEM table (1807236 records) and ITEM_PROD_DUMP table (796369 records).
I need to update two columns (total_volume_amount, total_volume_uom) from ITEM table with the values of second table ITEM_PROD_DUMP where their primary key (SYS_ITEM_ID) matches.

I have written a query to do so, it works but only for handful records. For these huge number of records, it just keeps on running.

Can anyone please help me to write a correct and optimal query.

Query I have written:

update item i set i.total_volume_amount = (select ipd.total_volume_amount
                                            from item_prod_dump ipd
                                            where i.sys_item_id = ipd.sys_item_id),
i.total_volume_uom = (select ipd.total_volume_uom
                    from item_prod_dump ipd
                    where i.sys_item_id = ipd.sys_item_id)
where exists (select ipd.total_volume_amount
            from item_prod_dump ipd
            where i.sys_item_id = ipd.sys_item_id);

>Solution :

Use a MERGE statement. Pure and simple. 1.8 million records is not a "huge" number of records.

merge into item t
using ( SELECT  *
        FROM    item_prod_dump ipd ) u
on ( t.sys_item_id = u.sys_item_id )
when matched then update set t.total_volume_amount = u.total_volume_amount,
                             t.total_volume_uom = u.total_volume_uom;