AIX 5.1, DB2 8.1.3 64-bit ESE 5 partitions 1 catalog, 4 data.
I have a situation where I have to update 269,000,000 rows in a table
with the value in another table with just about the same number of
records. It's a reporting table and the update is based on the
primary key and both tables are indexed to support to the look up.
Both tables also share the same partitioning key and are in the same
node group.
My first try was to declare a cursor to select the fields I need from
the base table, then loop though the cursor and update the records.
The performance was almost exactly 1000 rows per minute, so it was
going to take about 186 days to finish. Not good.
So since it's a partitioned database, I thought I'll connect to one
partition (export DB2NODE=X) and then change my cursor and update to
include where the dbpartitionnum(part key) = current dbpartitionnum.
Performance was the same. Still not good.
So then I wrote a corollatted update like
update report_table A set A.col = (select B.col from base_table B
where A.key = B.key) where exists (select * from base_table B where
A.key = B.key)
Performance still stunk.
So, then I though let me try a merge statement like
merge report table as rpt ( select key + one column to update from
base table ) base
on key when matched update rpt = base
All of sudden the post join performance of the update is 1,000,000
rows a minute and I'm going to finish in about 5 hours. What gives?
Can someone please explain to me what the merge is doing under the
covers that works so well?
Thanks!
