home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC77636 Status: Geschlossen

OPTIMIZER CHOOSES TABLE SCANS INSTEAD OF INDEX ON DPF WITH
DB2_INLIST_TO_NLJN

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - 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.7 and Fix Pack 5                    * 
****************************************************************
Local-Fix:
Use Optimization guidelines: 
 
 
<OPTGUIDELINES> 
<IXSCAN TABLE='"T_00"' INDEX='"TABLE1_IX"' /> 
</OPTGUIDELINES>
verfügbare FixPacks:
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Lösung
Problem was first fixed in DB2 Version 9.7 and Fix Pack 5
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
20.07.2011
10.12.2011
10.12.2011
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.
Problem behoben lt. FixList in der Version
9.7.0.5 FixList