|
Problem IC65698
|
Status: Closed |
POTENTIAL QUERY PERFORMANCE DEGRADATION INVOLVING JOIN PREDICATES THAT EQUATE THE NULL VALUE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
A new feature in Version 9.1 enables the DB2 query optimizer to
consider join predicates, that equate the null value, as index
start and stop keys. These join predicates have the following
form:
T1.A = T2.A OR (T1.A IS NULL AND T2.A IS NULL)
If the data in the join columns is significantly skewed, then
the optimizer may underestimate the cardinality of the join, and
choose a less-than-optimal access plan. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* A new feature in Version 9.1 enables the DB2 query optimizer *
* to *
* consider join predicates, that equate the null value, as *
* index *
* start and stop keys. These join predicates have the *
* following *
* form: *
* *
* *
* *
* T1.A = T2.A OR (T1.A IS NULL AND T2.A IS NULL) *
* *
* *
* *
* If the data in the join columns is significantly skewed, *
* then *
* the optimizer may underestimate the cardinality of the join, *
* and *
* choose a less-than-optimal access plan. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.7 Fix Pack 2 and enable the *
* improvement if needed. *
**************************************************************** |
| Local Fix: |
Defining a statistical view on the join will provide the
required statistics to allow the optimizer to choose an optimal
access plan. The following is an example of such a view
definition:
CREATE VIEW S.SVIEW1 AS
SELECT *
FROM T1,T2
WHERE T1.A1=T2.A2 OR (T1.A1 IS NULL AND T2.A2 IS NULL);
ALTER VIEW S.SVIEW1 ENABLE QUERY OPTIMIZATION;
RUNSTATS ON TABLE S.SVIEW1 WITH DISTRIBUTION; |
| available fix packs: |
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 6 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 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 was first fixed in Version 9.7 Fix Pack 2.
This fix addressed scenarios where a statistical view cannot be
used. To enable this improvement, the user must set the
following registry variable:
DB2_SELECTIVITY=JOIN_SKEW
If the variable is already set, then multiple settings can be
provided, separated by a comma. For example:
DB2_SELECTIVITY=YES,JOIN_SKEW |
| Workaround |
see LOCAL FIX |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.01.2010
18.05.2010
18.05.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
