home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

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
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
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 FixList