IT32533: DISTRIBUTIONS CREATED ON A TEMP TABLE USED IN A QUERY CAN SKEW COSTS AND OPTIMIZER MAY CHOSE BAD PLAN

APAR status

• Closed as program error.

Error description

• This problem was discovered by a customer running 11.70.FC8 on
  hpia64.  A subsequent repro shows
  the problem exists on linux x86-64 running 11.70.FC8,
  11.70.FC9W1 and 12.10.FC13
  
  Consider a temp table that was created as SELECTâ ¦FROMâ ¦INTO
  TEMP tmptab1
  
  When indexes are create on columns in tmptab1 then distributions
  are created on the index columns
  and can be used by the optimizer in queries that use the temp
  table.
  
  Distributions are also created when update statistics
  medium/high is run on the temp table.
  
  These distributions may cause the optimizer to calculate lower
  costs for undesirable plans and
  lead the optimizer into choosing a non optimal plan.
  
  In this particular case, the customer was able to work around
  the issue by not creating the index
  on the temp table or use optimizer directives to ensure the
  desired plan was chosen.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix 11.70.xC8 and earlier versions.            *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Consider a temp table that was created as SELECTâ ¦FROMâ  *
  * ¦INTO                                                       *
  * TEMP tmptab1.                                                *
  *                                                              *
  * When indexes are create on columns in tmptab1 then           *
  * distributions                                                *
  * are created on the index columns                             *
  * and can be used by the optimizer in queries that use the     *
  * temp                                                         *
  * table.                                                       *
  *                                                              *
  * Distributions are also created when update statistics        *
  * medium/high is run on the temp table.                        *
  *                                                              *
  * These distributions may cause the optimizer to calculate     *
  * lower                                                        *
  * costs for undesirable plans and                              *
  * lead the optimizer into choosing a non optimal plan.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• Upgrade to Informix 11.70.xC9W2.
  

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

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels