IT36781: OPTIMIZER IS CHOOSING SEQUENTIAL SCAN WITHIN QUERY PLAN OF 9 TABLE JOIN USING INNER AND LEFT OUTER JOINS

APAR status

• Closed as Permanent restriction.

Error description

• After upgrading to 12.10.FC14 from a 11.70.FC8 special build,
  customer observed a query was taking minutes
  to run when it used to run in under a second.
  
  The query is a 9 table join with 6 inner joins and 3 left outer
  joins.
  
  In the bad query plan, the optimizer is choosing a sequential
  scan on a larger table earlier in the
  plan and it becomes the basis for most of the time cost.
  
  Using an ORDERED join for this particular query provides a hint
  for optimizer to choose a path like the
  faster 11.70 plan.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * After upgrading to 12.10.FC14 from a 11.70.FC8 special build *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * optimizer is choosing sequential scan within query plan of 9 *
  * table join using inner and left outer joins                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• the customer seems to be happy with the workaround and if they
  decide to move to a higher server version than 12.10.xC14 they
  can try removing the workaround and see how the query performs.
  

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

Applicable component levels