|
Problem IC68450
|
Status: Closed |
DB2_ANTIJOIN, DB2 SHOULD CONVERT ELIGIBLE OUTER JOIN TO ANTIJOIN |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
To improve performance, when the registry variable DB2_ANTIJOIN
is set to anything other than NO, DB2 should convert an eligible
OUTER JOIN into an ANTIJOIN.
To be eligible, all the following must be true:
1. The OUTER JOIN must have an IS NULL predicate on a column
from the NULL producing side.
If the column is not the join column then it must be marked NOT
NULL.
2. Null producing side of the OUTER JOIN must be a base table
with no local predicates except for the IS NULL predicate.
3. It is not a federated query.
4. The select clause of the query must not reference any column
from the null producing side of the left outer join.
example:
select t1.* from t1 left outer join t2 on t1.c1 = t2.c1 where
t2.c2 IS NULL. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* In DB2, certain left outer join query constructs can be *
* safely converted into antijoin. This improves cardinality *
* estimation and performance. *
****************************************************************
* RECOMMENDATION: *
* Please upgrade to DB2 v9.7 fix pack 3a *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The fix is available in DB2 v9.7 fix pack 3a |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.05.2010
14.02.2011
14.02.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
