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