DB2 - Problembeschreibung
Problem IT05988 | Status: Geschlossen |
DB2 OPTIMIZER MIGHT CHOOSE SUBOPTIMAL ACCESS PLAN FOR SELECT STATEMENT WITH VALUES CLAUSE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
DB2 optimizer might choose suboptimal access plan for SELECT statement that defines a temporary table by using VALUES clause. For example, consider the following query: SELECT T1.C2, T1.C4, T1.C5 FROM T1, ( SELECT * FROM ( VALUES CAST ( ? AS VARCHAR(150) ), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) AS T2_TMP ( C1 ) GROUP BY C1 ) AS T2 WHERE T1.C1 = ? AND T1.C2 = T2.C1 AND T1.C3 = ? AND T1.C4 IN ( ?, ? ) There is an index on columns ( C1, C2, C3, C4, C5 ). In this case, if the join predicate T1.C2 = T2.C1 is very selective, then one of the best access plans to execute this query is NLJOIN plan with T1 on inner side (RHS) and T2 on outer side (LHS). The query optimizer may not choose the above mentioned NLJOIN plan. For example, a suboptimal plan that applies T1.C4 IN ( ?, ? ) predicate as in2join could be chosen. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.1 Fix Pack 5. * **************************************************************** | |
Local-Fix: | |
You can force the good NLJOIN plan by using optimization profile. | |
Lösung | |
First fixed in DB2 Version 10.1 Fix Pack 5. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 09.12.2014 17.07.2015 17.07.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.5 |