SQL Reference

SYSSTAT.INDEXES

Contains one row for each index that is defined for a table.

Table 106. SYSSTAT.INDEXES Catalog View
Column Name Data Type Nullable Description Updatable
INDSCHEMA VARCHAR(128)
Qualified name of the index.
INDNAME VARCHAR(18)

TABSCHEMA VARCHAR(128)
Qualifier of the table name.
TABNAME VARCHAR(128)
Name of the table or nickname on which the index is defined.
COLNAMES CLOB(1M)
List of column names with + or - prefixes.
NLEAF INTEGER
Number of leaf pages; -1 if statistics are not gathered.

This column can only be updated with the following values:

  • -1 or > 0 (zero)
Yes
NLEVELS SMALLINT
Number of index levels; -1 if statistics are not gathered.

This column can only be updated with the following values:

  • -1 or > 0 (zero)
Yes
FIRSTKEYCARD BIGINT
Number of distinct first key values; -1 if statistics are not gathered.

This column can only be updated with the following values:

  • -1 or >= 0 (zero)
Yes
FIRST2KEYCARD BIGINT
Number of distinct keys using the first two columns of the index (-1 if no statistics or inapplicable)

This column can only be updated with the following values:

  • -1 or >= 0 (zero)
Yes
FIRST3KEYCARD BIGINT
Number of distinct keys using the first three columns of the index (-1 if no statistics or inapplicable)

This column can only be updated with the following values:

  • -1 or >= 0 (zero)
Yes
FIRST4KEYCARD BIGINT
Number of distinct keys using the first four columns of the index (-1 if no statistics or inapplicable)

This column can only be updated with the following values:

  • -1 or >= 0 (zero)
Yes
FULLKEYCARD BIGINT
Number of distinct full key values; -1 if statistics are not gathered.

This column can only be updated with the following values:

  • -1 or >= 0 (zero)
Yes
CLUSTERRATIO SMALLINT
This is used by the optimizer. It indicates the degree of data clustering with the index; -1 if statistics are not gathered or if detailed index statistics have been gathered.

This column can only be updated with the following values:

  • -1 or between 0 and 100
Yes
CLUSTERFACTOR DOUBLE
This is used by the optimizer. It is a finer measurement of degree of clustering, or -1 if detailed index statistics have not been gathered.

This column can only be updated with the following values:

  • -1 or between 0 and 1
Yes
SEQUENTIAL_PAGES INTEGER
Number of leaf pages located on disk in index key order with few or no large gaps between them. (-1 if no statistics are available.)

This column can only be updated with the following values:

  • -1 or >= 0 (zero)
Yes
DENSITY INTEGER
Ratio of SEQUENTIAL_PAGES to number of pages in the range of pages occupied by the index, expressed as a percent (integer between 0 and 100, -1 if no statistics are available.)

This column can only be updated with the following values:

  • -1 or between 0 and 100
Yes
PAGE_FETCH_PAIRS VARCHAR(254)
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 index using that hypothetical buffer. (Zero-length string if no data available.)

This column can be updated with the following input values:

  • The pair delimiter and pair separator characters are the only non-numeric characters accepted
  • Blanks are the only characters recognized as a pair delimiter and pair separator
  • Each number entry must have an accompanying partner number entry with the two being separated by the pair separator character
  • Each pair must be separated from any other pairs by the pair delimiter character
  • Each expected number entry must between 0-9 (only positive values)
Yes

[ Top of Page | Previous Page | Next Page ]