There are certain guidelines that you should follow when
updating statistics in the SYSSTAT.TABLES catalog view.
- The only statistical values that you
can update in SYSSTAT.TABLES are CARD, FPAGES, NPAGES, AVGCOMPRESSEDROWSIZE,
AVGROWCOMPRESSIONRATIO, PCTROWSCOMPRESSED, OVERFLOW and, for multidimensional
clustering (MDC) tables, ACTIVE_BLOCKS.
- The value of the CARD statistic must meet the following criteria:
- It must be greater than or equal to all COLCARD statistic values
for the corresponding table in SYSSTAT.COLUMNS.
- It must be greater than the value of the NPAGES statistic.
- It must not be less than or equal to any "fetch" valuein
the PAGE_FETCH_PAIRS column of any index (assuming that the CARD statistic
is relevant to the index).
- The value of the FPAGES statistic must be greater than the value
of the NPAGES statistic.
- The value of the NPAGES statistic must be less than or equal to
any "fetch" value in the PAGE_FETCH_PAIRS column of any index
(assuming that the NPAGES statistic is relevant to the index).
- Valid values for the AVGCOMPRESSEDROWSIZE
statistic are -1 or between 0 and the value of the AVGROWSIZE statistic.
- Valid values for the AVGROWCOMPRESSIONRATIO
statistic are -1 or greater than 1.
- Valid values for the PCTROWSCOMPRESSED
statistic are -1 or 0 - 100, inclusive.
In a
federated database system, use caution when manually updating statistics
for a nickname over a remote view. Statistical information, such as
the number of rows that a nickname returns, might not reflect
the real cost of evaluating this remote view and therefore might mislead
the DB2® optimizer. In certain
cases, however, remote views can benefit from statistics updates;
these include remote views that you define on a single base table
with no column functions applied to the SELECT list. Complex views
might require a complex tuning process in which you tune each query.
Consider creating local views over nicknames so that the DB2 optimizer knows how to derive the cost of
those views more accurately.
DB2 10.5 for Linux, UNIX, and Windows