|
Problem IC64037
|
Status: Closed |
JOIN PLANNING HEURISTICS FOR NON-FILTERING TRANSITIVITY JOIN PRE DICATES MAY BE EFFECTIVE. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
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. |
| Local Fix: |
|
| available fix packs: |
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
|
| Solution |
module engn_sqno |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
08.03.2010
08.03.2010 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
