DB2 FOR LUW / DB2FORLUW / 970 - DB2

During fast join enumeration there are optimizations used to 
improve planning when there are unfiltering transitive join 
predicates in the query. Without these optimizations a poor 
choice for the first joins in the query may be made which can 
cause very large intermediate result sets and poor performance. 
For example: 
SELECT  a1, a2, a3, b2, b3 
FROM    a, b, c 
WHERE a1=b1 
    AND b1=c1 
    AND a2=b2 
    AND a3=c3; 
 
One possible choice for the above query is to join tables B and 
C using predicate (b1=c1) and then join that to table A using 
predicates (a1=b1 and a2=b2 and a3=c3). However if the number of 
distinct values in columns b1 and c1 is low, then joining B and 
C first in the manner can be a bad choice. Depending on the 
statistics, the fast join enumeration may decide that this is 
the lowest cost join and do it first. 
The optimizations as described in APAR IZ22365 may not be 
effective when the statistics do not indicate a FACT table 
joined to the various DIMESION tables or DIMENSION snowflakes. 
This APAR may be applicable if you are experiencing poor 
performance and are expecting the optimization described in 
IZ22365 to be effective for your queries. Generate and examine a 
db2exfmt output for the affected queries and determine if there 
a  JOIN operation using only a single transitive join predicate, 
for example b1=c1 from the example above, if so then this APAR 
may be applicable.

During fast join enumeration there are optimizations used to 
improve planning when there are unfiltering transitive join 
predicates in the query. Without these optimizations a poor 
choice for the first joins in the query may be made which can 
cause very large intermediate result sets and poor performance. 
For example: 
SELECT  a1, a2, a3, b2, b3 
FROM    a, b, c 
WHERE a1=b1 
    AND b1=c1 
    AND a2=b2 
    AND a3=c3; 
 
One possible choice for the above query is to join tables B and 
C using predicate (b1=c1) and then join that to table A using 
predicates (a1=b1 and a2=b2 and a3=c3). However if the number of 
distinct values in columns b1 and c1 is low, then joining B and 
C first in the manner can be a bad choice. Depending on the 
statistics, the fast join enumeration may decide that this is 
the lowest cost join and do it first. 
The optimizations as described in APAR IZ22365 may not be 
effective when the statistics do not indicate a FACT table 
joined to the various DIMESION tables or DIMENSION snowflakes. 
This APAR may be applicable if you are experiencing poor 
performance and are expecting the optimization described in 
IZ22365 to be effective for your queries. Generate and examine a 
db2exfmt output for the affected queries and determine if there 
a  JOIN operation using only a single transitive join predicate, 
for example b1=c1 from the example above, if so then this APAR 
may be applicable.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 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 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
 

module engn_sqno

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

22.10.2009
08.03.2010
08.03.2010