| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC88353 | Status: Closed |
QUERY RE-WRITER UNEXPECTEDLY RE-WRITES A BAD PERFORMANCE SQL | |
| product: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
| Problem description: | |
Equality join predicates may be pushed down thru an OLAP
function. The columns in the join predicates are required to be
a superset of a unique index (or a primary key). The join
predicates then will form a correlation, resulting in limiting
the only join method eligible be the NLJOIN. This could cause a
performance degradation.
An indicator that a user has hit this problem is to look at the
Optimized Statement of the db2exfmt output.
(SELECT ..., <func>(<col> OVER (PARTITION NY <col>,...), ...
FROM Q#3, ...
WHERE Q#3.<col> = Q#2.<col>
) AS Q#1,
<table> AS Q#2, ...
If a correlated join predicate such as Q#3.<col> = Q#2.<col> is
seen and self-join of the base table with unique key as:
FROM K.R46C AS Q3, K.R05C AS Q4,
K.R05C AS Q5, K.R41C AS Q6,
K.R01C AS Q7,
the Q4 and Q5 are quantifiers over the same base table.
This SQL statement may hit this problem. | |
| Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * DB2 UDB Version 10.2 * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Version 10.1 FixPack 2. * **************************************************************** | |
| Local Fix: | |
| available fix packs: | |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows | |
| Solution | |
Problem was first fixed in DB2 UDB Version 10.1 FixPack 2. | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 17.11.2012 19.12.2012 19.12.2012 |
| Problem solved at the following versions (IBM BugInfos) | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 10.1.0.2 |
|
| 10.5.0.2 |
|