SYS_INDEXES

Information on indexes in the database that were created with CREATE INDEX or implicitly with a UNIQUE constraint.

Column name

Data type

Contents

INDEX_SCHEMA

CHAR (31)

Name of the schema to which the index
belongs

TABLE_NAME

CHAR (31)

Name of the base table to which the index
belongs

COLUMN_NAME

CHAR (31)

Name of the column in the index

INDEX_NAME

CHAR (18)

Name of the index

INDEX_ID

SMALLINT

Identification number of the index

SPACE_NAME

CHAR (18)

Name of the space in which the index is
stored

SPACE_ID

SMALLINT

Identification number of the space in which
the index is stored

ORDINAL_POSITION

SMALLINT

Position of the column in the index

LENGTH_I

SMALLINT

Total length of the index (in bytes)

LENGTH_C

SMALLINT

Indicates the length (in bytes) to which the
column is included in the index

INDEX_DISTANCE

SMALLINT

Distance of the column to the index start

DATA_TYPE_C

VARCHAR (24)

Data type of the column:
CHARACTER
CHARACTER VARYING
NATIONAL CHAR
NATIONAL CHAR VARYING
REAL
DOUBLE PRECISION
FLOAT
INTEGER
SMALLINT
NUMERIC
DECIMAL
DATE
TIME
TIMESTAMP
OLDEST

CONSTRAINT_NAME

CHAR (31)

Name of the UNIQUE constraint if the
index is used by a UNIQUE constraint.

NULL value in all other cases

STATE

VARCHAR (9)

Status:
GENERATED
DEFECT

GENERATE_TYPE

VARCHAR (8)

as generated:
EXPLICIT
IMPLICIT

STATISTICS_INFO

VARCHAR (3)

YES	Statistics information exists
NO	Statistics information does not exist

INDEX_TYPE

VARCHAR (8)

Index type:
SINGLE
COMPOUND

INDEX_DATE

TIMESTAMP (3)

Time of generation

INDEX_PRIMARY_KEY

CHAR (1)

Y	Index is used for the compound key of CALL DML tables
N	else

TABLE_ID

SMALLINT

Identification number of the base table.
When TABLE_ID >= 30720 the table is a
partitioned table.

Table 116: SYS_INDEXES view of the SYS_INFO_SCHEMA (section 2 of 2)