|
Problem IC91223
|
Status: Closed |
INACCURATE DENSITY AND SEQUENTIAL_PAGES STATISTICS FOR PARTITIONED INDEXES MIGHT LEAD TO NON-OPTIMAL ACCESS PLANS |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
DENSITY for partitioned indexes might be inaccurate when the
partitioned table has a significant number of empty partitions.
SEQUENTIAL_PAGES for partitioned indexes might be inaccurate and
inconsistent with NLEAF when the partitioned table has a
significant number of empty or near empty partitions. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 10.1 Fixpack 3. *
**************************************************************** |
| Local Fix: |
1. Use optimization guidelines to direct the query optimizer to
different access plans.
2. Alternately, provide the query optimizer with more accurate
statistics via manually updating the relevant SYSSTAT.INDEXES
rows. To determine what statistical values to use, one approach
is to examine the per-partition index statistics in
SYSCAT.INDEXESPARTITIONS and use a value representative from the
majority of partitions with data. You can use the NUMRIDS
statistics to assess if a partition is empty or near empty.
When updating SEQUENTIAL_PAGES, a further consideration is its
relationship to NLEAF. For example, if the majority of data
holding partitions have
SYSCAT.INDEXESPARTITIONS.SEQUENTIAL_PAGES near
SYSCAT.INDEXESPARTITIONS.NLEAF, then update
SYSTAT.INDEXES.SEQUENTIAL_PAGES so that it is near
SYSTAT.INDEXES.NLEAF. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Fixed in Version 10.1 Fixpack 3. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 02.04.2013
10.10.2013
10.10.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.3
|
 |
| 10.1.0.3
|
|
