DB2 - Problembeschreibung
Problem IC64701 | Status: Geschlossen |
THE OPTIMIZER MAY CHOOSE A LESS-THAN-OPTIMAL ACCESS PLAN, UNDER CERTAIN CONDITIONS,FOR STATEMENTS CONTAINING SUBQUERY PREDICATES | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
The query optimizer may choose a less-than-optimal query access plan for SQL statements containing subquery predicates that are not optimized into a join, such as the following: . SELECT T1.C1 FROM T1 WHERE T1.C2 NOT IN (SELECT C2 FROM T2); SELECT T1.C1 FROM T1 WHERE T1.C2 > ? AND T1.C2 NOT IN (SELECT C2 FROM T2 WHERE C2 > ?); . If there is an index on T1 with T1.C2 as the leading column in the index key and the access path of T2 chosen by the optimizer to satisfy the subquery is ordered on C2 (for example, an index access with T2.C2 as the leading column in the key) the optimizer may over-estimate the cost associated with the index access on T1 which may lead to the optimizer favouring a less optimal access plan. To determine the access plan chosen by the optimizer, the EXPLAIN facility can be used. . This APAR corrects the optimizer's cost function for these scenarios. . | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * The query optimizer might choose a less-than-optimal query * * access * * plan for SQL statements containing subquery predicates that * * are * * not optimized into a join, such as the following: * * * * . * * * * SELECT T1.C1 * * * * FROM T1 * * * * WHERE T1.C2 NOT IN (SELECT C2 FROM T2); * * * * SELECT T1.C1 * * * * FROM T1 * * * * WHERE T1.C2 > ? AND T1.C2 NOT IN (SELECT C2 FROM T2 WHERE C2 * * > * * ?); * * * * . * * * * If there is an index on T1 with T1.C2 as the leading column * * in * * the index key and the access path of T2 chosen by the * * optimizer * * to satisfy the subquery is ordered on C2 (for example, an * * index * * access with T2.C2 as the leading column in the key) the * * * * optimizer may over-estimate the cost associated with the * * index * * access on T1 which may lead to the optimizer favouring a * * less * * optimal access plan. To determine the access plan chosen by * * the * * optimizer, the EXPLAIN facility can be used. * * * * . * * * * This APAR corrects the optimizer's cost function for these * * * * scenarios. * * * * . * **************************************************************** * RECOMMENDATION: * * Upgrade to Version 9.7 Fix Pack 1 * **************************************************************** | |
Local-Fix: | |
You can try to improve the performance of such queries by using one of the following techniques: 1. reduce the query optimization level using the "set current query optimization" statement 2. rewrite the NOT IN into a NOT EXISTS subquery; for example you can rewrite the query SELECT T1.C1 FROM T1 WHERE T1.C2 NOT IN (SELECT C2 FROM T2); into SELECT T1.C1 FROM T1 WHERE NOT EXISTS (SELECT C2 FROM T2 WHERE T2.C2=T1.C2); | |
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 | |
Workaround | |
see LOCAL FIX | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 19.11.2009 28.02.2010 28.02.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP1 | |
Problem behoben lt. FixList in der Version | |
9.7.0.1 |