Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC80944 Status: Closed

CARDINALITY MAY BE OVERESTIMATED WHEN THERE ARE MULTIPLE IN PRED ICATES ON
THE SAME COLUMNS AS MULTIPLE EQUALITY JOIN PREDICATE

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
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.
Problem Summary:
CARDINALITY MAY BE OVERESTIMATED WHEN THERE ARE MULTIPLE IN PRED 
ICATES ON THE SAME COLUMNS AS MULTIPLE EQUALITY JOIN PREDICATE
Local Fix:
available fix packs:
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Solution
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC84587 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
20.01.2012
07.06.2012
07.06.2012
Problem solved at the following versions (IBM BugInfos)
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.6 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.