DB2 FOR LUW / DB2FORLUW / A10 - DB2

The optimizer will consider an optimization for an SQL statement 
containing a MIN or MAX aggregation without a group by clause, 
referred to as a one-fetch group by operation, if the stream is 
ordered on the aggregation source column. You can identify if 
the group by operation is optimized as a one-fetch by collecting 
an EXPLAIN of the statement and searching for the ONEFETCH 
argument in the group by operator details: 
 
                ONEFETCH: (One Fetch flag) 
                        TRUE 
 
If the input cardinality is significantly underestimated to 
value of 1 or less, then the optimizer might not choose to 
perform this optimization, and the ONEFETCH argument will not 
exist in the formatted EXPLAIN output.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* DB2 OPTIMIZER MIGHT NOT CHOOSE A ONE-FETCH GROUP BY WHEN     * 
* CARDINALITY IS UNDERESTIMATED                                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V10 fixpack 4.                                * 
****************************************************************

In most cases, collecting up-to-date statistics will improve the 
cardinality estimate if the SQL statement contains predicates 
that qualify the latest data in the table and the statistics are 
stale.

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First Fixed in DB2 V10 FP4.

In most cases, collecting up-to-date statistics will improve the 
cardinality estimate if the SQL statement contains predicates 
that qualify the latest data in the table and the statistics are 
stale.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

11.10.2013
10.06.2014
10.06.2014