IBM Support

IC98267: SELECT MAX() CHOOSES QUERY PLAN WITH LOWER ESTIMATED COSTS, BUT LONGER RUN TIME

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

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