PH49929: A LESS EFFICIENT INDEX MAY BE SELECTED WHILE ANOTHER INDEX CAN PROVIDE BETTER FILTERING.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A less efficient index may be selected while another index can
  match a superset of the matching and screening predicates of
  this index, or can provide better matching filter factor.
  Db2 might choose inefficient index if certain conditions happen
  during processing the statement.
  The optimizer behaviour will be changed so the best performing
  index can be always chosen.
  
  -- for this sample table
  CREATE TABLE SYSADM.TAB0 (
   COL1 CHAR(10),       -- FF=1e-8
   COL2 CHAR(10),       -- FF=1e-7
   COL3 CHAR(10),       -- FF=1e-3
   COL4 CHAR(10),
   COL5 CHAR(10),
   COL6 CHAR(10),
   COL7 CHAR(10),
   COL8 CHAR(10),       -- FF=1e-1
   COL9 DECIMAL(10,0),  -- output
   COL10 CHAR(10),
   COL11 CHAR(10),
   COL12 CHAR(10)
  );
  
  
  -- inefficient sample no.1
  CREATE INDEX SYSADM.TESTIX1 ON SYSADM.TAB0 (
   COL2 ASC,  -- matching  FF=1e-7
   COL6 ASC,
   COL7 ASC,
   COL8 ASC,  -- screening FF=1e-1
   COL3 ASC   -- screening FF=1e-3
  );
  
  -- inefficient sample no.2
  -- has output but it has to access tablespace to apply all
  filter
  CREATE INDEX SYSADM.TESTIX2 ON SYSADM.TAB0 (
   COL2 ASC,  -- matching  FF=1e-7
   COL6 ASC,
   COL9 ASC,  -- output
   COL3 ASC,  -- screening FF=1e-3
   COL1 ASC   -- screening FF=1e-8
  );
  
  -- inefficient sample no.3
  CREATE INDEX SYSADM.TESTIX3 ON SYSADM.TAB0 (
   COL5 ASC   -- matching  FF=1e-7
  );
  
  -- should be chosen because it can provide best filtering
  CREATE INDEX SYSADM.TESTIX4 ON SYSADM.TAB0 (
   COL1 ASC,  -- matching  FF=1e-8
   COL2 ASC,  -- matching  FF=1e-7
   COL3 ASC,  -- matching  FF=1e-3
   COL4 ASC
  );
  
  -- sample query getting inefficient access plan
  SELECT COL9
  FROM SYSADM.TAB0
  WHERE COL10=?
    AND COL11=?
    AND COL1=?
    AND COL2=?
    AND COL12=?
    AND COL8=?
    AND COL7=?
    AND COL5=?
  ;
  
  ADDITIONAL SYMPTOMS:
  SQLPERFORMANCE SQLACCESSPATH FILTERFACTOR
  

Local fix

• BYPASS/CIRCUMVENTION:
  If you suspect you're affected by the issue, you may try to use
  optimization hint to choose better index. Explain the possible
  indexes using optimization hint and/or test their performance
  using optimization hint in order to find better match. If it's
  possible, and doesn't impact matching predicates of good index,
  you may also try to pushdown leading matching predicate of
  inefficient index, by adding 'OR 0=1' to appropriate predicate
  in WHERE clause.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 and Db2 13 for z/OS who                  *
  * have queries with 2 competing indexes in                     *
  * which one has better matching filter factor                  *
  * than the other.                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A less efficient index may be selected                       *
  * while another index has better                               *
  * matching filter factor.                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  A less efficient index may be selected while another index has
  better matching filter factor. This problem could occur when
  both indexes are very selective and the more efficient index
  has a poor cluster ratio.
  
  For example,
  
  SELECT *
  FROM T1
  WHERE T1.C1 = ?
  AND T1.C2 = ?
  AND T1.C3 = '02' ;
  
  There are 2 indexes defined on T1 - IX1 on (C1), IX2
  on (C2,C3). IX1 may be selected if:
  - the estimated filter factor of "T1.C1=? is very small--in
  particular, when IX1 has a better cluster ratio, and
  - the matching filter factor of IX2 is lower than IX1.
  

Problem conclusion

• Db2 has been changed to favor the index with better matching
  selectivity.
  Additional Keywords:
  SQLPERFORMANCE SQLACCESSPATH
  

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:

  UI83841 UI83842

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI83841

     UP22/12/29 P F212

• RD10 PSY UI83842

     UP22/12/29 P F212

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.