Hi all
We ran into a very annoying optimizer problem recently. We had added a
new key and self-join relation to a large production table. That key
will be filled very rarely and having just being added was practically
never filled in when the first user tried to delete a row from the table.
Now, the optimizer tried to enforce RI on the new relation. But instead
of using the index generated by the relation, it used a table scan!
Why? Here's IBM's answer (condensed):
----------------------------------------------------------------------
From what I have been seeing, this behavior is not a bug. The optimizer
is working as designed. The problem come from the fact that the new key
column does not have sufficent distinct values in the table. When
deciding if the index generated by the foreign key constraint
should be used or not, the optimizer calculates the filter factor of
that column. The filter factor is calculated by dividing the
column cardinality by number of rows in table (in your testcase,
1/1356534 which is close to 0). For an index to be considered useful by
DB2, this filter factor has be closer to 1. This is why optimizer
discarded that index as being usefull and chose a table scan. The best
way to circumvent the poor filter factor, would be to declare the table
as volatile (ALTER TABLE UMBNT.DOSSIERPOS VOLATILE CARDINALITY).
-------------------------------------------------------------------------------
I find this very unsatisfactory. During RI checking, and generally when
following relations, DB2 should know that it will never have to seek
NULL values in its indexes. Given detailed runstats, it also knows the
most frequent values and their cardinality for an index. So it should
know that the filter factor for this index, *excluding NULLs*, is
practically 1, not 0.
I don't think the scenario I have described is so rare. What's more, is
forces us to either drop the RI, or use the VOLATILE flag, which I'd
hate to do for a large table in a productive system.
Finally, it is a sort of behaviour that can break a running system just
because a key was added that no one uses yet anyway!
Am I alone in thinking that declaring this behaviour "as designed" is an
unsatisfactory stance?
Regards
Peter Arrenbrecht
Opus Software AG