SYSSTAT.INDEXES catalog view
Each row represents an index. Indexes on typed tables are represented by two rows: one for the "logical index" on the typed table, and one for the "H-index" on the hierarchy table.
Column Name | Data Type | Nullable | Updatable | 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. | ||
COLNAMES | VARCHAR (640) | This column is no longer used and will be removed in the next release. Use SYSCAT.INDEXCOLUSE for this information. | ||
NLEAF | BIGINT | Y | Number of leaf pages; -1 if statistics are not collected. | |
NLEVELS | SMALLINT | Y | Number of index levels; -1 if statistics are not collected. | |
FIRSTKEYCARD | BIGINT | Y | Number of distinct first-key values; -1 if statistics are not collected. | |
FIRST2KEYCARD | BIGINT | Y | Number of distinct keys using the first two columns of the index; -1 if statistics are not collected, or if not applicable. | |
FIRST3KEYCARD | BIGINT | Y | Number of distinct keys using the first three columns of the index; -1 if statistics are not collected, or if not applicable. | |
FIRST4KEYCARD | BIGINT | Y | Number of distinct keys using the first four columns of the index; -1 if statistics are not collected, or if not applicable. | |
FULLKEYCARD | BIGINT | Y | Number of distinct full-key values; -1 if statistics are not collected. | |
CLUSTERRATIO4 | SMALLINT | Y | Degree of data clustering with the index; -1 if statistics are not collected or if detailed index statistics are collected (in which case, CLUSTERFACTOR will be used instead). | |
CLUSTERFACTOR4 | DOUBLE | Y | Finer measurement of the degree of clustering; -1 if statistics are not collected or if the index is defined on a nickname. | |
SEQUENTIAL_PAGES | BIGINT | Y | 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 | Y | Ratio of SEQUENTIAL_PAGES to number of prefetched pages. Expressed as a percentage; -1 if statistics are not collected. | |
PAGE_FETCH_PAIRS4 | VARCHAR (520) | Y | 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 table with this index using that hypothetical buffer. Zero-length string if no data is available. | |
NUMRIDS4 | BIGINT | Y | Total number of row identifiers (RIDs) or block identifiers (BIDs) in the index; -1 if not known. | |
NUMRIDS_DELETED4 | BIGINT | Y | Total number of row identifiers (or block identifiers) in the index that are marked deleted, excluding those identifiers on leaf pages on which all the identifiers are marked deleted. | |
NUM_EMPTY_LEAFS | BIGINT | Y | Total number of index leaf pages that have all of their row identifiers (or block identifiers) marked deleted. | |
AVERAGE_RANDOM_ FETCH_PAGES1,2,4 | DOUBLE | Y | Average number of random table pages between sequential page accesses when fetching using the index; -1 if not known. | |
AVERAGE_RANDOM_ PAGES2 | DOUBLE | Y | Average number of random table pages between sequential page accesses; -1 if not known. | |
AVERAGE_SEQUENCE_ GAP2 | DOUBLE | Y | Gap between index page sequences. 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_GAP1,2,4 | DOUBLE | Y | Gap between table page sequences when fetching using the index. Detected through a scan of index leaf pages, each gap represents the average number of table pages that must be randomly fetched between sequences of table pages; -1 if not known. | |
AVERAGE_SEQUENCE_ PAGES2 | DOUBLE | Y | 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_PAGES1,2,4 | DOUBLE | Y | Average number of table pages that are accessible in sequence (that is, the number of table pages that the prefetchers would detect as being in sequence) when fetching using the index; -1 if not known. | |
AVGPARTITION_ CLUSTERRATIO3,4 | SMALLINT | Y | Degree of data clustering within a single data partition. -1 if the table is not partitioned, if statistics are not collected, or if detailed statistics are collected (in which case AVGPARTITION_ CLUSTERFACTOR will be used instead). | |
AVGPARTITION_ CLUSTERFACTOR3,4 | DOUBLE | Y | Finer measurement of the degree of clustering within a single data partition. -1 if the table is not partitioned, if statistics are not collected, or if the index is defined on a nickname. | |
AVGPARTITION_PAGE_ FETCH_PAIRS3,4 | VARCHAR (520) | Y | A list of paired integers in character form. Each pair represents a potential buffer pool size and the corresponding page fetches required to access a single data partition from the table. Zero-length string if no data is available, or if the table is not partitioned. | |
DATAPARTITION_CLUSTERFACTOR | DOUBLE | Y | A statistic measuring the "clustering" of the index keys with regard to data partitions. It is a number between 0 and 1, with 1 representing perfect clustering and 0 representing no clustering. | |
INDCARD | BIGINT | Y | Cardinality of the index. This might be different from the cardinality of the table for indexes that do not have a one-to-one relationship between the table rows and the index entries. | |
PCTPAGESSAVED | SMALLINT | Approximate percentage of pages saved in the index as a result of index compression. -1 if statistics are not collected. | ||
AVGLEAFKEYSIZE | INTEGER | Y | Average index key size for keys on leaf pages in the index. | |
AVGNLEAFKEYSIZE | INTEGER | Y | Average index key size for keys on non-leaf pages in the index. | |
Note:
|