DB2 - Problem description
Problem IC88478 | Status: Closed |
DB2 OPTIMIZER MIGHT NOT CHOOSE A ONE-FETCH GROUP BY WHEN CARDINALITY IS UNDERESTIMATED | |
product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problem description: | |
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. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 V97 FP9 * **************************************************************** | |
Local Fix: | |
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. | |
available fix packs: | |
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows | |
Solution | |
First fixed in DB2 V97 FP9 | |
Workaround | |
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. | |
BUG-Tracking | |
forerunner : APAR is sysrouted TO one or more of the following: IC96658 IC96826 follow-up : | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 21.11.2012 14.02.2014 14.02.2014 |
Problem solved at the following versions (IBM BugInfos) | |
9.7.FP9 | |
Problem solved according to the fixlist(s) of the following version(s) | |
9.7.0.9 | |
9.7.0.9 |