|
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) |
