|
Problem IC65078
|
Status: Closed |
AUTOMATICALLY REWRITE OR PREDICATE IN SELECT DISTINCT QUERY INTO UNION OPERATOR |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
An OR predicate that involves table join is evaluated as
Sargable predicate at runtime. OR predicate can already be
converted to UNION operator by DB2 query rewrite. As result each
OR subterm predicate can become efficient start-stop key
predicate. Such rewrite requires certain conditions. One of them
is the query must be SELECT with DISTINCT, and another is the OR
hosting SELECT operator must have no join predicate between OR
referenced tables and any other table. The latter condition is
relaxed by this enhancement. An example is
SELECT DISTINCT ... WHERE T1.X = T2.X AND (T1.Y = T3.Y AND
T2.Z=1 OR T1.Y = T4.Y AND T4.Z = 1). The SELECT has join
predicate T1.X=T2.X, in which T1 is referenced by OR predicate
but T2 is not. This enhancement enables the conversion from OR
to UNION. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users, *
****************************************************************
* PROBLEM DESCRIPTION: *
* An OR predicate that involves table join is evaluated *
* asSargable predicate at runtime. OR predicate can already *
* beconverted to UNION operator by DB2 query rewrite. As *
* resulteach OR subterm predicate can become efficient *
* start-stopkey predicate. Such rewrite requires certain *
* conditions. Oneof them is the query must be SELECT with *
* DISTINCT, andanother is the OR hosting SELECT operator must *
* have no joinpredicate between OR referenced tables and any *
* other table.The latter condition is relaxed by this *
* enhancement.An example is:SELECT DISTINCT ...WHERE T1.X = *
* T2.X AND (T1.Y = T3.Y AND T2.Z=1 OR T1.Y = T4.YAND T4.Z = *
* 1).The SELECT has join predicate T1.X=T2.X, in which T1 *
* isreferenced by OR predicate but T2 is not. This *
* enhancementenables the conversion from OR to UNION. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.5 Fix Pack 6 or later releasecontaining *
* the fix. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.5 Fix Pack 6. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC65082 IC68559
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 10.12.2009
14.06.2010
14.06.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
