|
Problem IT04291
|
Status: Closed |
IO COST OF NLJOIN CAN BE SIGNIFICANT LOWER THAN IO COST OF INNER(RIGHT) IXSCAN-FETCH LEG IN RARE SITUATIONS |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
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) |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 10.5 Fix Pack 5. *
**************************************************************** |
| Local Fix: |
|
| Solution |
First fixed in DB2 Version 10.5 Fix Pack 5. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 09.09.2014
20.04.2015
20.04.2015 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.5
|
 |
