DB2 FOR LUW / DB2FORLUW / A50 - DB2

IO cost for a NLJOIN can be much lower than the IO cost of the 
inner IXSCAN-FETCH leg, when the following conditions apply: 
* detailed statistics on indexes 
* local equality predicates on all index key columns 
* join predicate not included in that index, hence join is done 
via a sargable predicate in the FETCH 
 
The IO cost underestimation can lead to poor access plans chosen 
by optimizer. 
 
Example: 
                            NLJOIN 
                            (  17) 
                            19550.8 
                      IO:  902.736 
                     /--------+--------\ 
                   359                 243.801 
                 ^HSJOIN               FETCH 
                 (  18)                   (  22) 
                 27.7845               23555.5 
                    4                IO:  6663.77 
             /-----+-----\           /---+----\ 
           359          ...      110210     3.53472e+06 
         FETCH                   IXSCAN     TABLE: XXX 
         (  19)                     (  23)         T2 
         20.8093               72.0577        Q4 
            3                       15.7937 
      /----+-----\                 | 
    359            359         3.53472e+06 
  IXSCAN   TABLE: XXX    INDEX: XXX 
  (  20)              T1             I2 
 0.0982991        Q8            Q4 
     0 
    | 
    359 
INDEX: XXX 
    I1 
    Q8 
 
* IO cost of NLJOIN( 17) much lower than IO cost of inner side, 
FETCH( 22) 
* Cardinality of outer leg, HSJOIN( 18) is 359, hence we have to 
do 359 lookups on inner side, this should add to IO costs 
accordingly 
* Join predicate between Q8 and Q4 used in FETCH( 22), join 
predicate cannot be pushed down to IXSCAN( 23) as the join 
column is not included in that index: 
   (Q4.C2 = Q8.C2) 
* Equality predicate used in in IXSCAN( 23): 
   (Q4.C3 = 37)

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 Fix Pack 5.                      * 
****************************************************************

First fixed in DB2 Version 10.5 Fix Pack 5.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

09.09.2014
20.04.2015
20.04.2015