|
Problem IC88327
|
Status: Closed |
DB2 OPTIMIZER MIGHT CHOOSE NON-OPTIMAL NLJOIN WHEN DB2_INLIST_TO_NLJN IS ENABLED AND DETAILED INDEX STATISTICS COLLECTED |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
The DB2_INLIST_TO_NLJN registry variable causes the optimizer to
favour nested loop joins (NLJOIN) to join the list of values,
using the table that contributes the IN list as the inner table
in the join. If detailed index statistics are collected, which
is the default collection method if automatic statistics
collection is enabled, the optimizer might favour converting the
predicate with the IN list to a join, using an NLJOIN operation
where the join is not applied as a range delimiting predicate
and when the predicate does not reduce the stream significantly.
To identify if your query is impacted by this problem, you can
collect an EXPLAIN of the query and compare the I/O cost of the
NLJOIN operator and the inner FETCH operator; for example
30
NLJOIN
( 9)
5832.86
232.835
/--------+-------\
2 15
TBSCAN FETCH
( 10) ( 13)
0.00202749 9769.94
0 390.524
| /---+----\
2 4000 1.0e+06
SORT IXSCAN TABLE: DB2ADMIN
( 11) ( 14) T1
0.00145019 51.9437 Q7
0 2
| |
2 1.0e+096
TBSCAN INDEX: DB2ADMIN
( 12) IX1
4.2511e-05 Q7
0
|
2
TABFNC: SYSIBM
GENROW
Q3
The I/O of FETCH(13) is larger than the I/O of NLJN (9). If the
join predicate is not applied at IXSCAN (14), then the query is
likely affected. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users that have DB2_INLIST_TO_NLJN set and detailed *
* index statistics collected. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 10.1 Fix Pack 2. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Problem first fixed in DB2 Version 10.1 Fix Pack 2. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.11.2012
27.03.2013
27.03.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.2
|
 |
| 10.5.0.2
|
|
