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

Costly UPDATE to MQT base table

P: n/a
Friends:

I'm writing this on behalf of a colleague (really--this isn't an "I
have a friend" story) who has a vexing problem concerning the update of
a table that contributes to an MQT.

A simple update of a base table of the MQT with a compound predicate on
the table's PK and a non-key column costs close to two million
timerons:

UPDATE
X
SET
C1 = ?,
C2 = ?
...
WHERE
PK = ?
AND
NONKEY = ?;

Now, a slight change to this update (changing "WHERE PK = ?" to "WHERE
PK IN (?,?)" changes the plan and accordingly drops the cost down to
around 450 timerons! The IN update uses indexes as expected, but the
equality update ignores indexes that the optimizer should find quite
helpful, and chooses instead to do an FTS on still another base table
of the MQT.

Given this this brief description, can anyone relate to this situation?
If so, what did you do to solve it?

The environment is DB2 UDB LUW 8.2.3 on AIX 5.x, and MQT and INDEX DDL,
the true DML, and EXPLAINs for both the "good" and "bad" plans are
available by e-mail to those interested, but I didn't want to include
them here for brevity's sake. Trust that STATs have been run, physical
organization is good, all expected indexes (e.g., FK columns) exist,
etc.

Thanks as always,

--Jeff

Aug 23 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.