|
Problem IC69093
|
Status: Closed |
OPTIMIZER UNDER ESTIMATES PAGE FILTERING FOR COLUMN USED IN LOCA L PREDICATE AND ON THE INNER OF NESTED LOOP JOIN. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Under limited conditions, the optimizer will over estimate the
filtering effect of a join predicate when it is computing the
number of pages in an index or a table that will be needed by a
Nested Loop Join (NLJOIN) operator. This conditions are:
1) A column with a local predicate applied to it. For example,
TEST.YEAR = 2010
2) The column has statistics for which the High2Key statistic is
less then the predicate's literal value. For example, the
High2Key for TEST.YEAR is '2009'.
3) There is also a join predicate on the same column. For
example TEST.YEAR = RESULTS.YEAR
4) The column is included in an index, and that column is being
used as a Start/Stop key.
Under these conditions, when the optimizer is computing how many
pages of the index and table will be touched after the
start/stop key has been applied, it will incorrectly compute the
filtering effect to indicate that no leaf pages of the index,
and no pages of the table will be touched. |
| Problem Summary: |
Under limited conditions, the optimizer will over estimate the
filtering effect of a join predicate when it is computing the
number of pages in an index or a table that will be needed by a
Nested Loop Join (NLJOIN) operator. This conditions are:
1) A column with a local predicate applied to it. For example,
TEST.YEAR = 2010
2) The column has statistics for which the High2Key statistic is
less then the predicate's literal value. For example, the
High2Key for TEST.YEAR is '2009'.
3) There is also a join predicate on the same column. For
example TEST.YEAR = RESULTS.YEAR
4) The column is included in an index, and that column is being
used as a Start/Stop key.
Under these conditions, when the optimizer is computing how many
pages of the index and table will be touched after the
start/stop key has been applied, it will incorrectly compute the
filtering effect to indicate that no leaf pages of the index,
and no pages of the table will be touched. |
| Local Fix: |
update the High2Key to be >= the literal value in the local
predicate |
| available fix packs: |
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
|
| Solution |
apar fixed >= v97 fpk3 |
| Workaround |
update the High2Key to be >= the literal value in the local
predicate |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.06.2010
23.09.2010
23.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FPk3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
