|
Problem IC65082
|
Status: Closed |
IN DB2 V9.7, AUTOMATICALLY REWRITE OR PREDICATE IN SELECT DISTINCT QUERY INTO UNION OPERATOR |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
An OR predicate that involves a table join is evaluated as a
Sargable predicate at runtime.
The 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 a 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 with the mentioned query construction. *
****************************************************************
* PROBLEM DESCRIPTION: *
* An OR predicate that involves a table join is evaluated as *
* aSargable predicate at runtime.The OR predicate can already *
* be converted to UNION operatorby DB2 query rewrite. As *
* result each OR subterm predicatecan become efficient *
* start-stop key predicate.Such rewrite requires certain *
* conditions. One of them is thequery must be SELECT with *
* DISTINCT, and another is the ORhosting SELECT operator must *
* have no join predicate betweenOR referenced tables and any *
* other table.The latter condition is relaxed by this *
* enhancement. Anexample is:SELECT DISTINCT ...WHERET1.X = *
* T2.XAND (T1.Y = T3.Y AND T2.Z=1OR T1.Y = T4.Y AND T4.Z = *
* 1)The SELECT has a 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 fix pack 3 or another release of DB2 *
* whichcontains the fix. *
**************************************************************** |
| Local Fix: |
|
| 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 9a 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 3. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 10.12.2009
27.09.2010
27.09.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
|
|
