home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
14.10.xC11 FixList
12.10.xC16.X5 FixList
11.70.xC9.XB FixList
11.50.xC9.X2 FixList
11.10.xC3.W5 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

Informix - Problem description

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)