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.

Table 1. SYSSTAT.INDEXES Catalog View
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:
  1. When using DMS table spaces, this statistic cannot be computed.
  2. Prefetch statistics are not gathered during a LOAD...STATISTICS USE PROFILE, or a CREATE INDEX...COLLECT STATISTICS operation, or when the database configuration parameter seqdetect is turned off.
  3. AVGPARTITION_CLUSTERRATIO, AVGPARTITION_CLUSTERFACTOR, and AVGPARTITION_PAGE_FETCH_PAIRS measure the degree of clustering within a single data partition (local clustering). CLUSTERRATIO, CLUSTERFACTOR, and PAGE_FETCH_PAIRS measure the degree of clustering in the entire table (global clustering). Global clustering and local clustering values can diverge significantly if the table partitioning key is not a prefix of the index key, or when the table partitioning key and the index key are logically independent of each other.
  4. This statistic cannot be updated if the index type is 'XPTH' (an XML path index).
  5. Because logical indexes on an XML column do not have statistics, the SYSSTAT.INDEXES catalog view excludes rows whose index type is 'XVIL'.
  6. There is a limitation for small and medium indexes. The density column will have values, which are not counted as described above. This behavior will not impact optimizer costing.