SYSINDICES (IDS)

The sysindices system catalog table describes the indexes in the database. It contains one row for each index that is defined in the database.

Column	Type	Explanation
idxname	VARCHAR(128)	Name of index
owner	VARCHAR(32)	Name of owner of index (user informix for system catalog tables and username for database tables)
tabid	INTEGER	Unique identifying code of table
idxtype	CHAR(1)	Index type: U = Unique D = Duplicates allowed
clustered	CHAR(1)	Clustered or nonclustered index (C = Clustered)
levels	SMALLINT	Number of tree levels
leaves	INTEGER	Number of leaves
nunique	INTEGER	Number of unique keys in the first column
clust	INTEGER	Degree of clustering; smaller numbers correspond to greater clustering. The maximum value is the number of rows in the table, and the minimum value is the number of data pages in the table. This column is blank until UPDATE STATISTICS is run on the table.
nrows	FLOAT	Estimated number of rows in the table (zero until UPDATE STATISTICS is run on the table).
indexkeys	INDEX-KEYARRAY	Column can have up to three fields, in the format: procid, (col1, ... , coln), opclassid where 1 < n < 341
amid	INTEGER	Unique identifying code of the access method that implements this index. (Value = am_id for that access method in the sysams table.)
amparam	LVARCHAR	List of parameters used to customize the behavior of this access method.
collation	CHAR(32)	Collating order at the time of index creation.

Column

Type

Explanation

idxname

VARCHAR(128)

Name of index

owner

VARCHAR(32)

Name of owner of index (user informix for system catalog tables and username for database tables)

tabid

INTEGER

Unique identifying code of table

idxtype

CHAR(1)

Index type:
U = Unique
D = Duplicates allowed

clustered

CHAR(1)

Clustered or nonclustered index
(C = Clustered)

levels

SMALLINT

Number of tree levels

leaves

INTEGER

Number of leaves

nunique

INTEGER

Number of unique keys in the first column

clust

INTEGER

Degree of clustering; smaller numbers correspond to greater clustering. The maximum value is the number of rows in the table, and the minimum value is the number of data pages in the table. This column is blank until UPDATE STATISTICS is run on the table.

nrows

FLOAT

Estimated number of rows in the table (zero until UPDATE STATISTICS is run on the table).

indexkeys

INDEX-KEYARRAY

Column can have up to three fields, in the format: procid, (col1, ... , coln), opclassid where 1 < n < 341

amid

INTEGER

Unique identifying code of the access method that implements this index. (Value = am_id for that access method in the sysams table.)

amparam

LVARCHAR

List of parameters used to customize the behavior of this access method.

collation

CHAR(32)

Collating order at the time of index creation.

Tip:

This system catalog table is changed from Version 7.2 of Informix database servers. The earlier schema of this system catalog table is still available as a view and can be accessed under its original name: sysindexes.

Changes that affect existing indexes are reflected in this system catalog table only after you run the UPDATE STATISTICS statement.

The fields within the indexkeys columns have the following significance:

The procid (as in sysprocedures) appears only for a functional index on return values of a function defined on columns of the table.

The list of columns (col1, ... , coln) in the second field identifies the columns on which the index is defined. The maximum is language-dependent: up to 341 for an SPL or Java UDR; up to 102 for a C UDR.

The opclassid identifies the secondary access method that the database server used to build and to search the index. This is the same as the sysopclasses.opclassid value for the access method.

The tabid column is indexed and allows duplicate values. A composite index on the idxname, owner, and tabid columns allows only unique values.

Home

Top of Page

Contents

Index