DB2 - Problembeschreibung
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 | |
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 |