|
Problem IT36781
|
Status: Closed |
OPTIMIZER IS CHOOSING SEQUENTIAL SCAN WITHIN QUERY PLAN OF 9 TABLE JOIN USING INNER AND LEFT OUTER JOINS
|
| product: |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 |
| Problem 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. |
| 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: *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
****************************************************************
* 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: *
**************************************************************** |
| Comment |
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. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.05.2021
02.02.2023
02.02.2023 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
