Each row represents a partitioned index piece located on one data partition. Note that the index partition statistics represent one database partition if the table is created on multiple database partitions.
Column Name | Data Type | Nullable | Description |
---|---|---|---|
INDSCHEMA | VARCHAR (128) | Schema name of the index. | |
INDNAME | VARCHAR (128) | Unqualified name of the index. | |
TABSCHEMA | VARCHAR (128) | Schema name of the table or nickname on which the index is defined. | |
TABNAME | VARCHAR (128) | Unqualified name of the table or nickname on which the index is defined. | |
IID | SMALLINT | Identifier for the index. | |
INDPARTITIONTBSPACEID | INTEGER | Identifier for the index partition table space. | |
INDPARTITIONOBJECTID | INTEGER | Identifier for the index partition object. | |
DATAPARTITIONID | INTEGER | This corresponds to the DATAPARTITIONID found in the SYSCAT.DATAPARTITIONS view. | |
INDCARD | BIGINT | Cardinality of the index partition. This might be different from the cardinality of the corresponding data partition for partitioned indexes that do not have a one-to-one relationship between the data partition rows and the index entries. | |
NLEAF | BIGINT | Number of leaf pages in the index partition; -1 if statistics are not collected. | |
NUM_EMPTY_LEAFS | BIGINT | Total number of index leaf pages in the index partition that have all of their row identifiers (RIDs) or block identifiers (BIDs) marked deleted. | |
NUMRIDS | BIGINT | Total number of row identifiers (RIDs) or block identifiers (BIDs) in the index partition; -1 if not known. | |
NUMRIDS_DELETED | BIGINT | Total number of row identifiers (RIDs) or block identifiers (BIDs) in the index partition that are marked deleted, excluding those identifiers on leaf pages on which all the identifiers are marked deleted. | |
FULLKEYCARD | BIGINT | Number of distinct full-key values in the index partition; -1 if statistics are not collected. | |
NLEVELS | SMALLINT | Number of index levels in the index partition; -1 if statistics are not collected. | |
CLUSTERRATIO | SMALLINT | Degree of data clustering with the index partition; -1 in
either of the following situations:
|
|
CLUSTERFACTOR | DOUBLE | Finer measurement of the degree of clustering; -1 if statistics are not collected. | |
FIRSTKEYCARD | BIGINT | Number of distinct first-key values; -1 if statistics are not collected. | |
FIRST2KEYCARD | BIGINT | Number of distinct keys using the first two columns of the index key; -1 if statistics are not collected, or if not applicable. | |
FIRST3KEYCARD | BIGINT | Number of distinct keys using the first three columns of the index key; -1 if statistics are not collected, or if not applicable. | |
FIRST4KEYCARD | BIGINT | Number of distinct keys using the first four columns of the index key; -1 if statistics are not collected, or if not applicable. | |
AVGLEAFKEYSIZE | INTEGER | Average index key size for keys on leaf pages in the index partition; -1 if statistics are not collected. | |
AVGNLEAFKEYSIZE | INTEGER | Average index key size for keys on non-leaf pages in the index partition; -1 if statistics are not collected. | |
PCTFREE | SMALLINT | Percentage of each index page to be reserved during the initial building of the index partition. This space is available for data insertions after the index partition has been built. | |
PAGE_FETCH_PAIRS | VARCHAR (520) | A list of pairs of integers, represented in character form. Each pair represents the number of pages in a hypothetical buffer, and the number of page fetches required to scan the data partition with this index using that hypothetical buffer. Zero-length string if not data is available. | |
SEQUENTIAL_PAGES | BIGINT | Number of leaf pages located on disk in index key order with few or no large gaps between them; -1 if statistics are not collected. | |
DENSITY | INTEGER | Ratio of SEQUENTIAL_PAGES to number of pages in the range of pages occupied by the index partition, expressed as a percent (integer between 0 and 100); -1 if statistics are not collected. | |
AVERAGE_SEQUENCE_GAP | DOUBLE | Gap between index page sequences within the index partition. Detected through a scan of index leaf pages, each gap represents the average number of index pages that must be randomly fetched between sequences of index pages; -1 if not known. | |
AVERAGE_SEQUENCE_FETCH_GAP | DOUBLE | Gap between table page sequences when fetching using the index partition. Detected through a scan of index leaf pages, each gap represents the average number of data partition pages that must be randomly fetched between sequences of data partition pages; -1 if not known. | |
AVERAGE_SEQUENCE_PAGES | DOUBLE | Average number of index pages that are accessible in sequence (that is, the number of index pages that the prefetchers would detect as being in sequence); -1 if not known. | |
AVERAGE_SEQUENCE_FETCH_PAGES | DOUBLE | Average number of data partition pages that are accessible in sequence (that is, the number of data partition pages that the prefetchers would detect as being in sequence) when fetching using the index; -1 if not known. | |
AVERAGE_RANDOM_PAGES | DOUBLE | Average number of random data partition pages between sequential page accesses; -1 if not known. | |
AVERAGE_RANDOM_FETCH_PAGES | DOUBLE | Average number of random data partition pages between sequential page accesses when fetching using the index partition; -1 if not known. | |
STATS_TIME | TIMESTAMP | Y | Last time that any change was made to the recorded statistics for this index partition. The null value if no statistics are available. |
COMPRESSION | CHAR (1) | Specifies whether index compression is activated
|
|
PCTPAGESSAVED | SMALLINT | Approximate percentage of pages saved in the index as a result of index compression. -1 if statistics are not collected. |