SQL Reference

SYSCAT.INDEXES

Contains one row for each index (including inherited indexes where applicable) that is defined for a table.

Table 67. SYSCAT.INDEXES Catalog View
Column Name Data Type Nullable Description
INDSCHEMA VARCHAR(128)
Name of the index.
INDNAME VARCHAR(18)
DEFINER VARCHAR(128)
User who created the index.
TABSCHEMA VARCHAR(128)
Qualified name of the table or nickname on which the index is defined.
TABNAME VARCHAR(128)
COLNAMES VARCHAR(640)
List of column names, each preceded by + or - to indicate ascending or descending order respectively. Warning: This column will be removed in the future. Use SYSCAT.INDEXCOLUSE for this information.
UNIQUERULE CHAR(1)
Unique rule:

D = Duplicates allowed

P = Primary index

U = Unique entries only allowed

MADE_UNIQUE CHAR(1)

Y = Index was originally non-unique but was converted to a unique index to support a unique or primary key constraint. If the constraint is dropped, the index will revert to non-unique.

N = Index remains as it was created.


COLCOUNT SMALLINT
Number of columns in the key plus the number of include columns if any.
UNIQUE_COLCOUNT SMALLINT
The number of columns required for a unique key. Always <=COLCOUNT. < COLCOUNT only if there a include columns. -1 if index has no unique key (permits duplicates)
INDEXTYPE CHAR(4)
Type of index.

CLUS = Clustering

REG = Regular

ENTRYTYPE CHAR(1)

H = An index on a hierarchy table (H-table)

L = Logical index on a typed table

blank if an index on an untyped table


PCTFREE SMALLINT
Percentage of each index leaf page to be reserved during initial building of the index. This space is available for future inserts after the index is built.
IID SMALLINT
Internal index ID.
NLEAF INTEGER
Number of leaf pages; -1 if statistics are not gathered.
NLEVELS SMALLINT
Number of index levels; -1 if statistics are not gathered.
FIRSTKEYCARD BIGINT
Number of distinct first key values; -1 if statistics are not gathered.
FIRST2KEYCARD BIGINT
Number of distinct keys using the first two columns of the index (-1 if no statistics or inapplicable)
FIRST3KEYCARD BIGINT
Number of distinct keys using the first three columns of the index (-1 if no statistics or inapplicable)
FIRST4KEYCARD BIGINT
Number of distinct keys using the first four columns of the index (-1 if no statistics or inapplicable)
FULLKEYCARD BIGINT
Number of distinct full key values; -1 if statistics are not gathered.
CLUSTERRATIO SMALLINT
Degree of data clustering with the index; -1 if statistics are not gathered or if detailed index statistics are gathered (in which case, CLUSTERFACTOR will be used instead).
CLUSTERFACTOR DOUBLE
Finer measurement of degree of clustering, or -1 if detailed index statistics have not been gathered or if the index is defined on a nickname.
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.)
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.)
USER_DEFINED SMALLINT
1 if this index was defined by a user and has not been dropped; otherwise 0.
SYSTEM_REQUIRED SMALLINT

1 if this index is required for primary key or unique key constraint, OR if this is the index on the object identifier (OID) column of a typed table.

2 if this index is required for primary key or unique key constraint, AND this is the index on the object identifier (OID) column of a typed table.

0 otherwise.


CREATE_TIME TIMESTAMP
Time when the index was created.
STATS_TIME TIMESTAMP Yes Last time when any change was made to recorded statistics for this index. Null if no statistics available.
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 table with this index using that hypothetical buffer. (Zero-length string if no data available.)
MINPCTUSED SMALLINT
If not zero, then on-line index reorganization is enabled and the value is the threshold of minimum used space before merging pages.
REVERSE_SCANS CHAR(1)

Y = Index supports reverse scans

N = Index does not support reverse scans


INTERNAL_FORMAT SMALLINT
Encodes the internal representation of the index.
REMARKS VARCHAR(254) Yes User-supplied comment, or null.


[ Top of Page | Previous Page | Next Page ]