| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC77636 | Status: Closed |
OPTIMIZER CHOOSES TABLE SCANS INSTEAD OF INDEX ON DPF WITH DB2_INLIST_TO_NLJN | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problem description: | |
When registry variable DB2_INLIST_TO_NLJN is enabled, in DPF
environment, DB2 optimizer can choose a Table Scan on a
query of the following nature even though there is an index that
could be used.
Query:
SELECT *
FROM "TABLE1" T_00, (
SELECT *
FROM (VALUES CAST (? AS VARCHAR(72)), CAST (? AS
VARCHAR(72)), CAST (? AS
VARCHAR(72)), CAST (? AS VARCHAR(72)), CAST (? AS
VARCHAR(72)),
CAST (? AS VARCHAR(72)), CAST (? AS VARCHAR(72)),
CAST (? AS
VARCHAR(72)), CAST (? AS VARCHAR(72)), CAST (? AS
VARCHAR(72)),
CAST (? AS VARCHAR(72)), CAST (? AS VARCHAR(72)),
CAST (? AS
VARCHAR(72)), CAST (? AS VARCHAR(72)), CAST (? AS
VARCHAR(72)),
CAST (? AS VARCHAR(72)), CAST (? AS VARCHAR(72)),
CAST (? AS
VARCHAR(72)), CAST (? AS VARCHAR(72)), CAST (? AS
VARCHAR(72))) AS
T_01_TMP ("C_01")
GROUP BY "C_01") AS T_01
WHERE T_00."C_01" = T_01."C_01"
WITH UR
Plan chosen:
38.8155
NLJOIN
( 3)
208.919
177
/------+------\
2.22222 17.467
TBSCAN TBSCAN
( 4) ( 8)
0.0401435 99.371
0 85
| |
2.22222 6849
SORT TEMP
( 5) ( 9)
0.0396246 94.6416
0 85
| |
2.22222 6849
DTQ DTQ
( 6) ( 10)
0.0386532 92.6006
0 85
| |
20 6849
TBSCAN TBSCAN
( 7) ( 11)
0.000171107 90.6664
0 85
| |
20 6849
TABFNC: SYSIBM TABLE:
GENROW SCHEMA1.TABLE1
Q1 Q4
The desired plan should make use of existing Index on TABLE1:
36.3512
NLJOIN
( 3)
302.628
40
/---------+---------\
20 1.81756
TBSCAN FETCH
( 4) ( 7)
0.00939683 15.1393
0 2
| /----+----\
20 1.81756 6849
SORT IXSCAN TABLE: SCHEMA1
( 5) ( 8) TABLE1
0.00777578 7.57544 Q4
0 1
| |
20 6849
TBSCAN INDEX: SCHEMA1
( 6) TABLE1_IX
0.000171107 Q4
0
|
20
TABFNC: SYSIBM
GENROW
Q1 | |
| Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * This APAR is a improvement on the Fix for APAR IY94892 POOR * * PERFORMING QUERY ACCESS PLAN CHOSEN FOR INLIST-TO-JOIN * * TRANSFORMATION IN DPF ENVIRONMENT * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.7 and Fix Pack 5 * **************************************************************** | |
| Local Fix: | |
Use Optimization guidelines: <OPTGUIDELINES> <IXSCAN TABLE='"T_00"' INDEX='"TABLE1_IX"' /> </OPTGUIDELINES> | |
| available fix packs: | |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows | |
| Solution | |
Problem was first fixed in DB2 Version 9.7 and Fix Pack 5 | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 20.07.2011 10.12.2011 10.12.2011 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7. | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.7.0.5 |
|