APAR status
Closed as program error.
Error description
We have a query against a single table that does something like this: select max(col1) from table where col2 = ? and col3 = ? The primary key is on col1. There is a composite index on col2, col3, col4. The optimizer chooses the primary key index because it has the lowest cost and the query runs in minutes. When the query is directed to use the composite index, the query runs in sub-seconds and the cost is around 20x faster than that of using the other index. When the query is left alone to choose the primary key index, it reads all of the pages in the table. When the query is directed to use the composite index consisting of the filter columns, it reads minimal index and data pages. So in effect when the query chooses the primary key index it is effectively doing a sequential scan although it does not say that in the query plan. The above holds true when the query does not find any rows. If the query finds rows, the query performs well.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of 12.10.xC1 and 12.10.xC2. * **************************************************************** * PROBLEM DESCRIPTION: * * A query may have sub-optimal query plan under the following * * conditions: * * - query contains MIN or MAX aggregation * * - query has a single table and one or more predicates on the * * table * * - table has at least two indexes defined, one with index * * columns matching the columns used in the predicates, one * * with index column matching the MIN or MAX aggregate column * * - query does not return any rows (i.e. if wrong index is * * chosen, then entire index is scanned) * **************************************************************** * RECOMMENDATION: * * Update to IDS-12.10.xC3 * ****************************************************************
Problem conclusion
Problem Fixed In IDS-12.10.xC3
Temporary fix
Comments
APAR Information
APAR number
IC98267
Reported component name
INFORMIX SERVER
Reported component ID
5725A3900
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2013-12-11
Closed date
2014-11-07
Last modified date
2014-11-07
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
INFORMIX SERVER
Fixed component ID
5725A3900
Applicable component levels
RC10 PSN
UP
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"C10","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
07 November 2014