Informix - Problem description
Problem IT32533 | Status: Closed |
DISTRIBUTIONS CREATED ON A TEMP TABLE USED IN A QUERY CAN SKEW COSTS AND OPTIMIZER MAY CHOSE BAD PLAN | |
product: | |
INFORMIX SERVER / 5725A3900 / B70 - IDS 11.70 | |
Problem 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. | |
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: * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * 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: * **************************************************************** | |
Comment | |
Upgrade to Informix 11.70.xC9W2. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 12.04.2020 24.08.2020 31.08.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |