DB2 FOR LUW / DB2FORLUW / 970 - DB2

The DB2 query optimizer may mis-estimate the join cardinality of 
an in-to-join transformation in a DPF environment, when the 
table that the IN predicate applies to is hash partitioned 
across two or more database partition, and the SQL statement 
includes predicates that bound the table access a single 
database partition.  For example, consider a table T hash 
partitioned on column A.  When optimizing the an SQL statement 
such as the following: 
 
select * 
from T 
where A = ? and B in (?,?) 
 
the optimizer recognizes that the predicate "A=?" will only 
exist on a single database partition since the table is hash 
partitioned on A; that is, it recognizes that the table is bound 
to a single database partition. In such a scenario, if the 
optimizer considers an in-to-join transformation for the 
predicate "B in (?,?)", it may mis-estimate the cardinality 
which could lead to a less-than-optimal access plan. 
 
Users Affected: DPF only

THE DB2 OPTIMIZER MAY MIS-ESTIMATE THE JOIN CARDINALITY OF AN 
IN-TO-JOIN TRANSFORMATION IN A DPF ENVIRONMENT

n/a

DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

09.05.2010
13.10.2010
13.10.2010