IC62006: THE OPTIMIZER CHOOSES A MUCH SLOWER PATH FOR SOME SUBQUERIES WHILE THE BETTER PATH IS GIVEN A MUCH HIGHER COST

APAR status

• Closed as program error.

Error description

• The type of subquery that may see this problem is a select that
  should take the same path regardless of whether the select is
  run as a standalone query or a subquery.  The problem is that
  the query takes a different, much slower path when run as a
  subquery. This is a regression from 9.40 .
  
  The workaround is to use an optimizer directive to force the
  subquery to take the same path (the same join order) as the path
  taken when the subquery is run as a standalone query.
  

Local fix

• Use an optimizer directive to force the subquery to choose the
  higher cost path.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 10.00.xC8 and later, 11.10.xC2 and later, 11.50.xC1 *
  * and later.                                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A complex query with nested subquery may have sub-optimal    *
  * query plan if the following conditions are met:              *
  *                                                              *
  * Both a subquery and its nested subquery are correlated       *
  * subqueries.                                                  *
  * The nested subquery correlates with the main query (its      *
  * grand parent), not the subquery (its parent).                *
  *                                                              *
  * For example, if you have a main query, subq1, subq2, where   *
  * subq1 is a subquery in main query, and subq2 is a subquery   *
  * inside subq1 (nested), and both subq1 and subq2 are          *
  * correlated subqueries, and in case of both subq1 and subq2,  *
  * the correlation is with the main query.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS 11.50.xC6W2 or above.                         *
  ****************************************************************
  

Problem conclusion

• The problem was first fixed in IDS 11.50.xC6W2.
  

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:

  IT01456

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP