|
Problem IC61604
|
Status: Closed |
EXECUTE INNER JOIN PREDICATE BEFORE OUTER JOIN PREDICATE IN ONE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The filtering inner join predicate is desirable to be evaluated
before the outer join predicate from query performance point of
view. This APAR fix aims at such query that has one fact table
joins with multiple dimension tables. The join types are mixed
with INNER JOIN and LEFT OUTER JOIN or RIGHT OUTER JOIN. The
following INNER JOIN predicate will be evaluated earlier than
OUTER JOIN
"fact_table.col1 = dim_tablei.col1 and <DATE type literal>
between dim_tablei.col2 and dim_tablei.col3"
if dim_tablei has unique index on dim_table1(col1, col2, col3)
and there exists no pair of rows sharing the same
(col1, col2). col2 and col3 must be of DATE type. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 V9.7 fixpack 0 *
****************************************************************
* PROBLEM DESCRIPTION: *
* The filtering inner join predicate is desirable to be *
* evaluatedb *
* efore the outer join predicate from query performance point *
* of v *
* iew. This APAR fix aims at such query that has one fact *
* table j *
* oins with multiple dimension tables. The join types are *
* mixed w *
* ith INNER JOIN and LEFT OUTER JOIN or RIGHT OUTER JOIN. The *
* f *
* ollowing INNER JOIN predicate will be evaluated earlier than *
* O *
* UTER JOIN *
* *
* *
* " *
* fact_table.col1 = dim_tablei.col1 and <DATE type literal> *
* b *
* etween dim_tablei.col2 and dim_tablei.col3" *
* *
* *
* i *
* f dim_tablei has unique index on dim_table1(col1, col2, *
* col3) a *
* nd there exists no pair of rows sharing the same *
* ( *
* col1, col2). col2 and col3 must be of DATE type. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to v9.7 fixpack 1 *
**************************************************************** |
| Local Fix: |
|
| 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 |
Fixed in v9.7 fixpck 1 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.06.2009
17.02.2010
17.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.,
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
