| Neueste Versionen | 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 |
|
| Haben Sie Probleme? - Kontaktieren Sie uns. | |
| Kostenlos registrieren |
|
| Kontaktformular |
|
DB2 - Problembeschreibung
| Problem IC77634 | Status: Geschlossen |
OPTIMIZER CHOOSES TABLE SCANS INSTEAD OF INDEX ON DPF WITH DB2_INLIST_TO_NLJN | |
| Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
| Problembeschreibung: | |
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-Zusammenfassung: | |
**************************************************************** * 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.5 and Fix Pack 9 * **************************************************************** | |
| Local-Fix: | |
Use Optimization guidelines: <OPTGUIDELINES> <IXSCAN TABLE='"T_00"' INDEX='"TABLE1_IX"' /> </OPTGUIDELINES> | |
| verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows | |
| Lösung | |
Problem was first fixed in DB2 Version 9.5 and Fix Pack 9 | |
| Workaround | |
keiner bekannt / siehe Local-Fix | |
| Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 20.07.2011 10.03.2012 10.03.2012 |
| Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.0., 9.5. | |
| Problem behoben lt. FixList in der Version | |
| 9.5.0.9 |
|