IC70293: OPTIMIZER MIGHT CHOOSE SEQUENTIAL SCAN INSTEAD OF FUNCTIONAL INDEX WHEN IN CLAUSE HAS MANY VALUES

APAR status

• Closed as program error.

Error description

• If a value set is evaluated by a functional index, it's
  depending on the number of values
  if the functional index or a sequential scan is used.
  
  For example, a query chooses a functional index when a value set
  consists of four values.
  However, the same query with a value set of eight values chooses
  a sequential scan.
  Inserting these eight values in a temporary table, and using
  that temp table in a subselect, will avoid the sequential scan.
  Using a regular index instead of a functional index will avoid
  the sequential scan, too.
  
  This problem can be observed using OPTCOMPIND 2,
  when using OPTCOMPIND 0 the functional index is used regardless
  of the number of values in the IN clause.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All 11.50 users.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query with predicates with functions may have sub-optimal  *
  * query plans if there exists an IN predicate with multiple    *
  * values in the IN clause, such as "function(col) IN           *
  * (<const1>, <const2>, ...)".                                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.50.xC8.                                        *
  ****************************************************************
  

Problem conclusion

• The problem is fixed in 11.50.xC8 in situations where there
  exists an index with a single index key, and the index key
  exactly matches the function used in the predicate. For
  example,
  
  create index idx on tab(function(col));
  

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:

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP