|
Problem IC61930
|
Status: Closed |
FOR DB2 UDB, CHANGE LEFT OUTER JOIN TO INNER JOIN WHILE QUERY HAS LOCAL "OR" PREDICATE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
If query has a local predicate on top of a left outer join
block, and if the local predicate refers to columns output from
the inner stream of the left outer join operator, then this left
outer join can be converted to inner join. This will give the
query more opportunities to be optimized by other DB2
transformations.
For example, a left outer join query is
select * from table1 left outer join table2 on table1.col1 =
table2.col1 where table2.col2 = 1 or table2.col3 = 1;
DB2 query rewrite will automatically change it to
select * from table1 inner join table2 on table1.col1 =
table2.col1 where table2.col2 = 1 or table2.col3 = 1; |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Problem Description above. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 Fix Pack 1. *
**************************************************************** |
| Local Fix: |
Rewrite the query statement. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
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 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
|
| Solution |
Problem first fixed in Version 9.7 Fix Pack 1. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.07.2009
18.01.2010
18.01.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
