By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

merge performance

P: n/a
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!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Interesting.
The answer will lie in the plans.
1) W.r.t. the concurrent cursor
Does the plan show colocation?
Could it be the source was coming from one partition only, limiting
the paralleism you could get out of it.
2) Update.
You state you the join was on a key column.
DB2 should have rewritten the query to a simple join, updating the
target leg. It shouldn't have been slower than the merge after that
rewrite. need to see the plan.
3) Merge
I'm impressed, given that you are on FP3.
You should see further improvement whenyou go to FP5 or even better
FP6. FP3 isn't exploiting have the tricks possible.

Cheere
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
One more thought about MERGE vs. UPDATE.
In UPDATE the target will be the outer of the join
In MERGE the target will be the inner of the outer join

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.