|
Problem IC64000
|
Status: Closed |
IN DB2 LUW, YOU MAY ENCOUNTER POOR PERFORMANCE OF QUERY WITH CORRELATED NLJOIN OUTER LEG |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The DB2 SQL Optimizer may choose a less-than-optimal access
plan in cases where an SQL query contains one or more complex
correlated Nested Loop Joins (NLJOIN). The following is an
example of a query that could result in an access plan that
contains a complex correlated NLJOIN:
SELECT * FROM T1 WHERE EXISTS(SELECT 1 FROM T2, T3 WHERE T1.C1
=T2.C1 and T2.C2 = T3.C2)
You are likely to experience this problem if the following
conditions are met:
1. When the db2exfmt output, Control Center Visual Explain, or
Optimum Query Tuner Query Access Plan is examined, the
correlated predicate is placed on the outer leg (left input) of
a NLJOIN, and is applied using an Index Scan (IXSCAN) applying
the predicate(s) as a Start/Stop key against the index.
2. The Start/Stop Predicates must filter the index down to a
small subset of the values in the index.
3. The index must contain more than 1 page. The larger the
index, the more likely you are to encounter this problem. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* The DB2 SQL Optimizer may choose a less-than-optimal access *
* *
* plan in cases where an SQL query contains one or more *
* complex *
* correlated Nested Loop Joins (NLJOIN). The following is *
* an *
* example of a query that could result in an access plan *
* that *
* contains a complex correlated NLJOIN: *
* *
* SELECT * FROM T1 WHERE EXISTS(SELECT 1 FROM T2, T3 WHERE *
* T1.C1 *
* =T2.C1 and T2.C2 = T3.C2) *
* *
* *
* *
* You are likely to experience this problem if the following *
* *
* conditions are met: *
* *
* *
* *
* 1. When the db2exfmt output, Control Center Visual Explain, *
* or *
* Optimum Query Tuner Query Access Plan is examined, the *
* *
* correlated predicate is placed on the outer leg (left input) *
* of *
* a NLJOIN, and is applied using an Index Scan (IXSCAN) *
* applying *
* the predicate(s) as a Start/Stop key against the index. *
* *
* *
* *
* 2. The Start/Stop Predicates must filter the index down to a *
* *
* small subset of the values in the index. *
* *
* *
* *
* 3. The index must contain more than 1 page. The larger the *
* *
* index, the more likely you are to encounter this problem. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 Fix Pack 1. *
**************************************************************** |
| Local Fix: |
n/a |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
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 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 1. At a
minimum, this fix should be applied at the server. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
24.02.2010
24.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
