APAR status
Closed as Permanent restriction.
Error 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.
Local fix
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: * ****************************************************************
Problem conclusion
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.
Temporary fix
Comments
APAR Information
APAR number
IT36781
Reported component name
INFORMIX SERVER
Reported component ID
5725A3900
Reported release
C10
Status
CLOSED PRS
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2021-05-04
Closed date
2023-02-02
Last modified date
2023-02-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Applicable component levels
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"C10","Line of Business":[{"code":"LOB10","label":"Data and AI"}]}]
Document Information
Modified date:
02 February 2023