|
Problem IC89682
|
Status: Closed |
POOR QUERY PERFORMANCE IF JOIN PREDICATE APPLIED TO THE FIRST AND LAST COLUMNS OF AN INDEX WITH INTERMEDIATE LOCAL PREDICATES |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Query performance may be poor when the query contains at least
three (3) tables joined together using INNER or OUTER joins and
there are at least 2 equal join predicates and at least two
local equality predicate.
The last table in the join sequence must have multicolumn
index covering the columns involved in the predicates. The
equality join predicates must be applicable on the first and
last columns of the index with the local eer quality predicate
applicable on the intermediate columns of the index.
Under these conditions the optimizer may incorrectly calculate
the cost of the joins leading to a poorly performing plan. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.7 fixpack 9 *
**************************************************************** |
| Local Fix: |
Use an optimization profile to obtain a better join order
or
Change the index definition so that the join predicate are
applicable to either the leading or trailing columns of the
index without any intermediate local equality predicates. |
| available fix packs: |
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 DB2 version 9.7 fixpack 9 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC92082 IC95288
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.01.2013
30.12.2013
30.12.2013 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP9
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.9
|
 |
| 9.7.0.9
|
|
