|
Problem IC66961
|
Status: Closed |
A TWO-WAY JOIN QUERY WITH OR JOIN PREDICATE MAY BE REWRITTEN TO UNION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
An OR join predicate is a residual predicate in query access
plan. If the OR predicate is composed of two selective subterm
predicates applied to of join base tables for each, and if the
query output columns has unique index and the subterm predicate
column has index, the entire query may be rewritten to UNION
form where the subterm predicates are distributed to UNION
branch. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* The fix is to improve the performance of a type of OR *
* predicate such that it is composed of two subterms each of *
* which is applied to a different single quantifier. Without *
* the fix, the OR predicate will have to be evaluated as an *
* expensive residual predicate after all the other predicates *
* no matter if the OR predicate is filtering or not. The fix *
* can get a cheaper index scan on each OR subermn predicate to *
* reduce data read at earlier time. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 3 *
**************************************************************** |
| Local Fix: |
Manually rewrite the query |
| available fix packs: |
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 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 |
First fixed in DB2 Version 9.7 Fix Pack 3. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC69861
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.03.2010
08.11.2010
08.11.2010 |
| 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.3
|
 |
| 9.7.0.3
|
|
