|
Problem IC68629
|
Status: Closed |
OPTIMIZE UNION ALL JOIN PUSHDOWN AND BRANCH ELIMINATION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
DB2 Rewrite will optimize the performance of union all views by
performing pushdown of local predicates of a base table which
joins the union all view if the join is considered to be non
expanding.
For the rewrite to take place, all the following conditions must
be true:
1. If we have join predicates from base table that cover all the
columns in a unique index of Base Table, and,
2. those columns, via equality classes in various levels, can be
mapped to all the branches of the UA that also ranges over the
same base table (BT)
3. There must be no recursion or correlation in the joins
related to the rewrite.
4. The union all must not have a Update, Delete, Insert
consumer, fetch n rows or be part of a subquery.
5. The joins with the base table must not be correlated
6. There must be no side-effects, external action or scratchpad
below the UNION ALL
Result:
Local predicates on base table are pushed down to all the base
tables under Union All view. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* DB2 Rewrite will optimize the performance of union all views *
* by *
* performing pushdown of local predicates of a base table *
* which *
* joins the union all view if the join is considered to be non *
* *
* expanding. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v970 fixpack 4 *
**************************************************************** |
| 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 enhancement is available in DB2 v970 fixpack 4 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 13.05.2010
29.04.2011
29.04.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.0
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
