Running Bulk Updates Faster using DML Merge.

Friday, February 12, 2016

Currently I have been having problem with running Update on a big scale tables. It runs awfully terribly slow. Once tried creating a new table with the updated information, and then truncating table and inserting the data from the new table but it is a hastle. I know there must be a way. Finally I found this site (http://www.orafaq.com/node/2450) where he tested all the possible solutions. Without going any detail, here is the best solution to update a large scale table with WHERE condition.

ALTER SESSION ENABLE PARALLEL DML;

MERGE /*+ first_rows parallel(test) parallel(new_set) */ INTO test
USING test5 new_set ON (test.pk = new_set.pk)
WHEN MATCHED THEN UPDATE SET
fk = new_set.fk,
fill = new_set.fill

This works wonder! I hope it will work for you too.