PI86134: INEFFICIENT SPARSE INDEX ACCESS PATH CHOSEN WITH OF1R

APAR status

• Closed as program error.

Error description

• An inefficient access path can be selected with a query
  containing OF1R. Db2 may choose a sparse index plan when an
  an equal unique index exists.
  
  Additional keywords and symptoms:
  SQLPERFORMANCE SQLSPARSEINDEX SQLOF1R
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of DB2 11 or 12 for z/OS                           *
  * who run queries with an OPTIMIZE FOR                         *
  * 1 ROW or FETCH FIRST 1 ROW ONLY clause.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * For queries with an OPTIMIZE FOR 1                           *
  * ROW or FETCH FIRST 1 ROW ONLY clause,                        *
  * DB2 may choose sparse index to access                        *
  * the inner table when there are index                         *
  * plans available with good matching                           *
  * predicates. It may cause a query                             *
  * performance problem.                                         *
  *                                                              *
  * SQLACCESSPATH SQLFFNR                                        *
  * SQLOFNR SQLSPARSEINDEX                                       *
  * SPARSEINDEX SQLPERFORMANCE                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  For queries with an OPTIMIZE FOR 1 ROW or FETCH FIRST 1 ROW
  ONLY clause, DB2 may choose sparse index to access the inner
  table when there are index plans available with good matching
  predicates.
  
  Compared with an index plan with good matching predicates, the
  sparse index plan cannot fetch the first several rows
  as efficiently. Therefore it should not get a cost reduction
  for the OPTIMIZE FOR 1 ROW or FETCH FIRST 1 ROW ONLY
  clause.
  

Problem conclusion

• The problem of the incorrect cost reduction for a sparse index
  plan is fixed by recognizing that the index plan with good
  matching predicates exists.
  

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:

  UI52926 UI52927

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY

     UP