DB2 FOR LUW / DB2FORLUW / 970 - DB2

Improve the optimization of start schema outer join query that 
has outer join of fact table and dimension tables and group by 
columns including all join key. The fact table group by operator 
can be executed prior to the table join operator. 
 
Here is an example scenario: 
 
-- FACT and DIMENSION TABLE 
create table dim1 (id1 int not null primary key, c11 int); 
create table dim2 (id2 int not null primary key, c21 int); 
create table fact (id1 int not null, id2 int not null, sale 
int); 
 
-- Referential Integrity 
alter table fact add constraint fk1 foreign key (id1) references 
dim1(id1); 
alter table fact add constraint fk2 foreign key (id2) references 
dim2(id2); 
 
-- QUERY 
select fact.id1, fact.id2, c11, c21, sum(sale) 
from fact left join dim1 on fact.id1=dim1.id1 left join dim2 on 
fact.id2=dim2.id2 
group by fact.id1, fact.id2, c11, c21; 
 
In query access plan, operator GRPBY used to be seen above JOIN 
among FACT, DIM1 and DIM2. With the fix, there still be one 
GRPBY operator but it will be done after TBSCAN or IXSCAN of 
FACT and before any join operator.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of V9.7 on Linux, Unix and Windows platforms.      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Improve the optimization of start schema outer join query    * 
* that has outer join of fact table and dimension tables and   * 
* group by columns including all join key. The fact table      * 
* group by operator can be executed prior to the table join    * 
* operator.                                                    * 
*                                                              * 
* Here is an example scenario:                                 * 
*                                                              * 
* -- FACT and DIMENSION TABLE                                  * 
* create table dim1 (id1 int not null primary key, c11 int);   * 
* create table dim2 (id2 int not null primary key, c21 int);   * 
* create table fact (id1 int not null, id2 int not null, sale  * 
* int);                                                        * 
*                                                              * 
* -- Referential Integrity                                     * 
* alter table fact add constraint fk1 foreign key (id1)        * 
* references dim1(id1);                                        * 
* alter table fact add constraint fk2 foreign key (id2)        * 
* references dim2(id2);                                        * 
*                                                              * 
* -- QUERY                                                     * 
* select fact.id1, fact.id2, c11, c21, sum(sale)               * 
* from fact left join dim1 on fact.id1=dim1.id1 left join dim2 * 
* on fact.id2=dim2.id2                                         * 
* group by fact.id1, fact.id2, c11, c21;                       * 
*                                                              * 
* In query access plan, operator GRPBY used to be seen above   * 
* JOIN among FACT, DIM1 and DIM2. With the fix, there still be * 
* one GRPBY operator but it will be done after TBSCAN or       * 
* IXSCAN of FACT and before any join operator.                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB Version 9.7 FixPak 1 or higher levels.    * 
****************************************************************

Manually replace "left join" with "inner join" in the query 
statement.

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
 

First fixed in DB2 UDB Version 9.7 FixPak 1.

Manually replace "left join" with "inner join" in the query 
statement.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

19.11.2009
17.12.2009
17.12.2009

9.7.