During planning the DB2 optimizer can make adjustments to the
estimated cardinality of the plan to account for highly
correlated values between columns using in multiple predicate of
the query. When there are multiple IN predicate and multiple
join predicate on the same columns, the adjustments made can
cause the the optimizer to overestimate the cardinality of the
plan. This overestimation can lead to poor plan choices and high
cost estimates.
For example the following query show an example of a query that
may be affected.
SELECT *
FROM T1, T2
WHERE T2.a = T1.a
and T2.b = T1.b
and T1.a IN (1, 2, 3, 4, 5)
and T1.b IN (1, 2, 3, 4, 5)
In this query there are two join predicates on columns A and B
between tables T1 and T2, in addition there are IN predicates on
both columns A and B. If the data in the table shows a high
degree of correlation between the values in columns A and B then
this query may be subject to the cardinality overestimation.
Using db2exfmt for suspected queries you can verify if this is
the case by looking for the predicate pattern noted above and
also noticing that the estimated cardinality is drastically
increasing at the actual NLJOIN, HSJOIN or MSJOIN operation
between tables T1 and T2. |
