|
Problem IC64701
|
Status: Closed |
THE OPTIMIZER MAY CHOOSE A LESS-THAN-OPTIMAL ACCESS PLAN, UNDER CERTAIN CONDITIONS,FOR STATEMENTS CONTAINING SUBQUERY PREDICATES |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* 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); |
| available fix packs: |
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
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 1 |
| Workaround |
see LOCAL FIX |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 19.11.2009
28.02.2010
28.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
