DB2 FOR LUW / DB2FORLUW / 970 - DB2

The DB2 Query Optimizer may misestimate the cardinality for 
queries that contain effectively cartesian predicates and use 
statistical views that don't reference these predicates. For 
example, consider the following query: 
 
select * from T1, T2 
where T1.A=T2.A and T1.B=1 and T2.B=1 
 
The two predicates T1.B=1 and T2.B=1 imply a third predicate 
T1.B=T2.B. Thus, at the join operation, the join on T1.B=T2.B is 
effectively a cartesian product of two result sets that have all 
1s in the join columns. If there is an applicable statistical 
view containing the predicate T1.A=T2.A and not T1.B=T2.B, then 
the optimizer may misestimate the cardinality of the join of T1 
and T2, which may result in a sub-optimal query execution plan.

USERS AFFECTED: ALL 
 
PROBLEM DESCRIPTION: 
 
see ERROR DESCRIPTION. 
 
PROBLEM SUMMARY: 
 
see ERROR DESCRIPTION.

Try to use SELECTIVITY clause to adjust cardinality.

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
 

The complete fix for this problem first appears in DB2 UDB 
Version 9.7 FixPak 3.

not known / see Local fix

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

07.09.2009
24.09.2010
24.09.2010

9.7.