PI92078: NONMATCHING INDEX INSTEAD OF A MATCHING INDEX MAY BE CHOSEN WHEN THEY HAVE SIMILAR IMFFADJ, CLUSTERRATIO AND COMPCOST.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Nonmatching index scan instead of the matching primary index may
  be chosen when they having very similar IMFFADJ, CLUSTERRATIO
  and COMPCOST. And the matching column(s) in the matching index
  is the supper set of the nonmatching index.
  
  Sample query and index:
  SELECT   TEST.COL1
         , TEST.COL2
         , TEST.COL3
         , TEST.COL4
         , TEST.COL5
         , TEST.COL6
         , TEST.COL7
         , TEST.COL8
         , TEST.COL9
         , TEST.COL10
         , TEST.COL11
         , TEST.COL12
         , TEST.COL13
  FROM     TABLE1 TEST
  WHERE    TEST.COL1 = :WORKTABLE1.COL1
  FOR      FETCH ONLY
  WITH     UR
  
  Matching index:
  CREATE UNIQUE  INDEX "I0"."INDEX0"
      ON "I0"."TABLE1"
       ("COL1" ASC                      <--Matching
       ,"COL2" ASC)
      ...
  
  Nonmatching index:
  CREATE INDEX "I0"."INDEX3"
      ON "I0"."TABLE1"
       ("COL13" ASC
       ,"COL12" DESC
       ,"COL1" ASC                     <--Screening
       ,"COL5" ASC))
       ...
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 11 and Db2 12 for z/OS who                  *
  * have queries which contain one or more                       *
  * predicates.                                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * DB2 may choose an inefficient index                          *
  * access path when the query contains                          *
  * one or more predicates.                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  When a query contains one or more predicates, Db2 may select an
  inefficient non-matching index when another index can match the
  predicates.
  
  Example:
  SELECT C1 FROM T
  WHERE C1=?;
  
  Say there are two indexes on T, IX1(C1,C3), IX2(C2,C1).
  For IX1, the predicate C1=? can be matching. For IX2, the
  predicate is screening. If both indexes get small cost
  estimates and the cost of IX2 is just slightly lower than IX1,
  DB2 may select index access using IX2, even though IX1
  provides matching index access.
  

Problem conclusion

• Code was updated to select an efficient index in the situation
  described above.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI55127 UI55128

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI55127

     UP18/04/27 P F804

• RB10 PSY UI55128

     UP18/04/27 P F804

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.