Statistical information about the size of database tables, indexes, and statistical views is stored in system catalog tables.
Statistic | Description | RUNSTATS Option | |
---|---|---|---|
Table | Indexes | ||
FPAGES | Number of pages being used by a table | Yes | Yes |
NPAGES | Number of pages containing rows | Yes | Yes |
OVERFLOW | Number of rows that overflow | Yes | No |
CARD | Number of rows in a table (cardinality) | Yes | Yes (Note 1) |
ACTIVE_BLOCKS | For MDC tables, the total number of occupied blocks | Yes | No |
Note:
|
Statistic | Description | RUNSTATS Option | |
---|---|---|---|
Table | Indexes | ||
COLCARD | Column cardinality | Yes | Yes (Note 1) |
AVGCOLLEN | Average length of a column | Yes | Yes (Note 1) |
HIGH2KEY | Second highest value in a column | Yes | Yes (Note 1) |
LOW2KEY | Second lowest value in a column | Yes | Yes (Note 1) |
NUMNULLS | The number of null values in a column | Yes | Yes (Note 1) |
SUB_COUNT | The average number of sub-elements | Yes | No (Note 2) |
SUB_DELIM_LENGTH | The average length of each delimiter separating sub-elements | Yes | No (Note 2) |
Note:
|
Statistic | Description | RUNSTATS Option | |
---|---|---|---|
Table | Indexes | ||
COLGROUPCARD | Cardinality of the column group | Yes | No |
Statistic | Description | RUNSTATS Option | |
---|---|---|---|
Table | Indexes | ||
TYPE | F = Frequency value |
Yes | No |
ORDINAL | Ordinal number of the column in the group | Yes | No |
SEQNO | Sequence number n that represents the nth TYPE value | Yes | No |
COLVALUE | The data value as a character literal or a null value | Yes | No |
Statistic | Description | RUNSTATS Option | |
---|---|---|---|
Table | Indexes | ||
TYPE | F = Frequency value |
Yes | No |
SEQNO | Sequence number n that represents the nth TYPE value | Yes | No |
VALCOUNT | If TYPE = F, VALCOUNT is the number of occurrences of COLVALUE for the column group with this SEQNO. If TYPE = Q, VALCOUNT is the number of rows whose value is less than or equal to COLVALUE for the column group with this SEQNO. | Yes | No |
DISTCOUNT | If TYPE = Q, this column contains the number of distinct values that are less than or equal to COLVALUE for the column group with this SEQNO. Null if unavailable. | Yes | No |
Statistic | Description | RUNSTATS Option | |
---|---|---|---|
Table | Indexes | ||
NLEAF | Number of index leaf pages | No | Yes |
NLEVELS | Number of index levels | No | Yes |
CLUSTERRATIO | Degree of clustering of table data | No | Yes (Note 2) |
CLUSTERFACTOR | Finer degree of clustering | No | Detailed (Notes 1,2) |
DENSITY | Ratio (percentage) of SEQUENTIAL_PAGES to number of pages in the range of pages that is occupied by the index (Note 3) | No | Yes |
FIRSTKEYCARD | Number of distinct values in the first column of the index | No | Yes |
FIRST2KEYCARD | Number of distinct values in the first two columns of the index | No | Yes |
FIRST3KEYCARD | Number of distinct values in the first three columns of the index | No | Yes |
FIRST4KEYCARD | Number of distinct values in the first four columns of the index | No | Yes |
FULLKEYCARD | Number of distinct values in all columns of the index, excluding any key value in an index for which all record identifiers (RIDs) are marked deleted | No | Yes |
PAGE_FETCH_PAIRS | Page fetch estimates for different buffer sizes | No | Detailed (Notes 1,2) |
AVGPARTITION_CLUSTERRATIO | Degree of data clustering within a single data partition | No | Yes (Note 2) |
AVGPARTITION_CLUSTERFACTOR | Finer measurement of degree of clustering within a single data partition | No | Detailed (Notes 1,2) |
AVGPARTITION_PAGE_FETCH_PAIRS | Page fetch estimates for different buffer sizes, generated on the basis of a single data partition | No | Detailed (Notes 1,2) |
DATAPARTITION_CLUSTERFACTOR | Number of data partition references during an index scan | No (Note 6) | Yes (Note 6) |
SEQUENTIAL_PAGES | Number of leaf pages located on disk in index key order, with few, or no large gaps between them | No | Yes |
AVERAGE_SEQUENCE_PAGES | Average number of index pages that are accessible in sequence; this is the number of index pages that the prefetchers can detect as being in sequence | No | Yes |
AVERAGE_RANDOM_PAGES | Average number of random index pages between sequential page accesses | No | Yes |
AVERAGE_SEQUENCE_GAP | Gap between sequences | No | Yes |
AVERAGE_SEQUENCE_FETCH_PAGES | Average number of table pages that are accessible in sequence; this is the number of table pages that the prefetchers can detect as being in sequence when they fetch table rows using the index | No | Yes (Note 4) |
AVERAGE_RANDOM_FETCH_PAGES | Average number of random table pages between sequential page accesses when fetching table rows using the index | No | Yes (Note 4) |
AVERAGE_SEQUENCE_FETCH_GAP | Gap between sequences when fetching table rows using the index | No | Yes (Note 4) |
NUMRIDS | The number of RIDs in the index, including deleted RIDs | No | Yes |
NUMRIDS_DELETED | The total number of RIDs in the index that are marked deleted, except RIDs on those leaf pages where all RIDs are marked deleted | No | Yes |
NUM_EMPTY_LEAFS | The total number of leaf pages on which all RIDs are marked deleted | No | Yes |
INDCARD | Number of index entries (index cardinality) | No | Yes |
Note:
|
Statistic | Description | RUNSTATS Option | |
---|---|---|---|
Table | Indexes | ||
DISTCOUNT | If TYPE = Q, DISTCOUNT is the number of distinct values that are less than or equal to COLVALUE statistics | Distribution (Note 2) | No |
TYPE | Indicator of whether the row provides frequent-value or quantile statistics | Distribution | No |
SEQNO | Frequency ranking of a sequence number to help uniquely identify the row in the table | Distribution | No |
COLVALUE | Data value for which a frequency or quantile statistic is collected | Distribution | No |
VALCOUNT | Frequency with which the data value occurs in a column; for quantiles, the number of values that are less than or equal to the data value (COLVALUE) | Distribution | No |
Note:
|