|
Problem IC64000
|
Status: Geschlossen |
IN DB2 LUW, YOU MAY ENCOUNTER POOR PERFORMANCE OF QUERY WITH CORRELATED NLJOIN OUTER LEG |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* 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 |
| verfügbare FixPacks: |
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
|
| Lösung |
Problem was first fixed in Version 9.7 Fix Pack 1. At a
minimum, this fix should be applied at the server. |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 22.10.2009
24.02.2010
24.02.2010 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.FP1
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.1
|
 |
