|
Problem IC80342
|
Status: Closed |
COLUMN GROUP STATISTICS NOT ALWAYS APPLIED TO CYCLE OF 3 OR MORE JOINS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Column Group Statistics (CGS) might not be considered by the DB2
optimizer when accounting for statistical correlation in a cycle
of 3 or more tables due to inconclusive parent and child
determination. For the optimizer to correct for statistical
correlation using CGS, one table must have a column cardinality
that is greater than or equal to the other table for all
columns involved in the CGS, and it must have a high2key/low2key
range for each column that is inclusive of the other table.
For non-cyclic joins, these conditions are not critical;
however, when 3 or more tables are involved in a cycle of joins,
if these conditions are not met for 1 pair of tables in the
cycle, the optimizer might end up correcting for statistical
correlation for some of the joins, and not others. This can
lead the optimizer to favour a join between the tables that have
not had their cardinality corrected.
For example, consider the query:
select * from T1, T2, T3 where T1.C1 = T2.D1 and
T1.C1 = T3.E3 and T2.D1 = T3.E1 and T1.C2 = T2.D2 and T1.C2 =
T3.E3 and T2.D2 = T3.E3
with statistics for the 3 tables and their columns as follows:
Table.Column Col Card Hi2Key Lo2Key
T1.C1 100 99 1
T1.C2 10 9 1
T2.D1 50 74 26
T2.D2 4 7 4
T3.E1 40 89 41
T3.E3 5 4 1
In the example, T1 has a higher column cardinality and inclusive
Hi2Key, Lo2Key when comparing T1.C1 = T2.D2 and T1.C1 = T3.E3,
and when comparing T1.C2 = T2.D2 and T1.C2 = T2.E2. The problem
is observed when comparing T2.D1 = T3.E1 and T2.D2 = T3.D3 where
the column cardinalities and the {Hi2Key, Lo2Key} range are
conflicting. Under these conditions, the optimizer will not
account for statistical correlation for the T2/T3 join, leading
to the optimizer potentially favouring this join.
The fix is activated using the DB2 environment variable:
DB2_CORRELATED_PREDICATES='FORCE_CGS' |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All DB2 for LUW users with complex joins *
****************************************************************
* PROBLEM DESCRIPTION: *
* Column Group Statistics (CGS) might not be considered by the *
* DB2 *
* optimizer when accounting for statistical correlation in a *
* cycle *
* of 3 or more tables due to inconclusive parent and child *
* determination. For the optimizer to correct for statistical *
* correlation using CGS, one table must have a column *
* cardinality *
* that is greater than or equal to the other table for all *
* columns involved in the CGS, and it must have a *
* high2key/low2key *
* range for each column that is inclusive of the other table. *
* *
* For non-cyclic joins, these conditions are not critical; *
* however, when 3 or more tables are involved in a cycle of *
* joins, *
* if these conditions are not met for 1 pair of tables in the *
* cycle, the optimizer might end up correcting for statistical *
* correlation for some of the joins, and not others. This can *
* lead the optimizer to favour a join between the tables that *
* have *
* not had their cardinality corrected. *
* *
* For example, consider the query: *
* *
* select * from T1, T2, T3 where T1.C1 = T2.D1 and *
* T1.C1 = T3.E3 and T2.D1 = T3.E1 and T1.C2 = T2.D2 and T1.C2 *
* = *
* T3.E3 and T2.D2 = T3.E3 *
* *
* with statistics for the 3 tables and their columns as *
* follows: *
* *
* Table.Column Col Card Hi2Key Lo2Key *
* T1.C1 100 99 1 *
* T1.C2 10 9 1 *
* T2.D1 50 74 26 *
* T2.D2 4 7 4 *
* T3.E1 40 89 41 *
* T3.E3 5 4 1 *
* *
* In the example, T1 has a higher column cardinality and *
* inclusive *
* Hi2Key, Lo2Key when comparing T1.C1 = T2.D2 and T1.C1 = *
* T3.E3, *
* and when comparing T1.C2 = T2.D2 and T1.C2 = T2.E2. The *
* problem *
* is observed when comparing T2.D1 = T3.E1 and T2.D2 = T3.D3 *
* where *
* the column cardinalities and the {Hi2Key, Lo2Key} range are *
* conflicting. Under these conditions, the optimizer will not *
* account for statistical correlation for the T2/T3 join, *
* leading *
* to the optimizer potentially favouring this join. *
* *
* The fix is activated using the DB2 environment variable: *
* DB2_CORRELATED_PREDICATES='FORCE_CGS' *
****************************************************************
* RECOMMENDATION: *
* Upgrade to version 9.7 Fix Pack 6 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in version 9.7 Fix Pack 6 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 09.12.2011
13.06.2012
13.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
