|
Problem IC61598
|
Status: Closed |
THE DB2 QUERY OPTIMIZER MAY UNDERESTIMATE THE JOIN COST, POTENTIALLY LEADING TO A SUB-OPTIMAL QUERY ACCESS PLAN |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
If your SQL statement contains an equality join predicate
between two tables, such as the one in the WHERE clause in
the following example
.
SELECT ...
FROM T1, T2 ...
WHERE ... T1.COLUMN1 = T2.COLUMN2 ...
.
and distribution statistics are collected, the optimizer could
take advantage of the distribution statistics in combination
with the high2key and low2key statistics during join cost
computation.
.
If the number of distinct values in either COLUMN1 or COLUMN2
is less than 3, then the optimizer may underestimate the cost
of the join.
.
The number of distinct values is described by the COLCARD column
in the SYSSTAT.COLUMNS catalog view. |
| Problem Summary: |
see APAR text. |
| Local Fix: |
1) Use an optimizer profile to force the desired access plan, OR
2) Update the LOW2KEY of the column with the smaller number of
distinct values to the LOW2KEY of the column with the higher
number of distinct values. |
| 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 |
Problem first fixed in DB2 UDB 9.7 Fixpack 1. |
| Workaround |
see APAR text |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 19.06.2009
04.02.2010
04.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
