|
Problem IC96632
|
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 / A50 - 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 incorrect 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 10.5 fixpack 3 *
**************************************************************** |
| 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 10.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in DB2 version 10.5 fixpack 3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.10.2013
27.02.2014
27.02.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.3
|
 |
| 10.5.0.3
|
|
