DB2 FOR LUW / DB2FORLUW / A50 - DB2

Performance may be poor for queries involving COLUMN ORGANIZED 
table that are referenced by cartesian join predicates. 
Cartesian join predicates are typically present when each of the 
tables involved in the join have a local equality predicate with 
the same constant value. For example a query 
 
SELECT c1, c2 FROM T1, T2, T3 where T1.c1='A' and T2.d1 = 'A' 
and T1.c2=T3.e2 and T2.d3 = T3.e3 
 
will have an cartesian join predicate 'T1.c1 = T2.d1', depending 
on the statistics present the optimizer may choose a plan where 
T1 is joined with T2 before T3. 
 
 
Examining the access plan produced by db2exfmt, affected queries 
will typically have a series of joins between tables and one or 
more of those HSJOINs will show an estimated cardinality that is 
larger then both of the estimated input cardinalities. For such 
a HSJOIN operator, the details will show the predicate with 
filter factor 1.0. 
 
There may also be a FILTER operator higher in the plan where a 
non-cartesian predicate between the same two tables is applied.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Problem Description above.                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 Fix Pack 6                       * 
****************************************************************

Use an optimization profile to force a different join order 
where the effectively cartesian join is delayed or where the 
order of the joins is swapped to allow the non-cartesian 
predicate to be applied early.

First fixed in DB2 Version 10.5 Fix Pack 6.

not known / see Local fix

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

25.03.2015
01.09.2015
01.09.2015