SYSINDICES

The sysindices system catalog table describes the indexes in the database. It stores LOW mode statistics for all indexes, and contains one row for each index that is defined in the database.

Table 1. **sysindices** system catalog table columns
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)	Uniqueness status U = Unique values required D = Duplicates allowed
clustered	CHAR(1)	Clustered or nonclustered status (C = Clustered)
levels	SMALLINT	Number of tree levels
leaves	FLOAT	Number of leaves
nunique	FLOAT	Number of unique keys in the first column
clust	FLOAT	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	INDEXKEYARRAY	Internal representation of the index keys. Column can have up to three fields, in the format: procid, (col1,col2, . . . , 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(2048)	List of parameters used to customize the amid access method behavior
collation	CHAR(32)	Database locale whose collating order was in effect at the time of index creation
pagesize	INTEGER	Size of the page, in bytes, where this index is stored
nhashcols	SMALLINT	Number of hashed columns in a FOT index
nbuckets	SMALLINT	Number of subtrees (buckets) in a forest of trees (FOT) index
ustlowts	DATETIME YEAR TO FRACTION	Date and time when index statistics were last recorded
ustbuildduration	INTERVAL HOUR TO FRACTION(5)	Time required to calculate index statistics
nupdates	FLOAT	Number of updates to the table
ndeletes	FLOAT	Number of deletes to the table
ninserts	FLOAT	Number of inserts to the table
fextsize	INT	Size (in KB) of the first extent of the index
nextsize	INT	Size (in KB) of the next extent of the index
indexattr	INT	0x00000001 = The index has a partial column key 0x00000002 = The index is compressed 0x00000004 = The index is on a BSON column
jparam	LVARCHAR(2048)	BSON index information

Tip: This system catalog table is changed from Version 7.2 of IBM® Informix®. The earlier schema of this system catalog table is still available as a view that can be accessed under its original name: sysindexes. See 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) exists only for a functional index on return values of a function defined on columns of the table.
The list of columns (col1, col2, ... , 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.

For information about the nupdates, ndeletes, and ninserts columns, which in sysindices tabulate DML operations on an index since the most recent recalculation of its distribution statistics, see the description of the three columns that have the same names in the SYSDISTRIB system catalog table.

The fextsize column shows the user-defined first extent size (in kilobytes) that the optional EXTENT SIZE clause specified in the CREATE INDEX statement that defined the index. Similarly, the nextsize column shows the user-defined next extent size (in kilobytes) that the optional NEXT SIZE clause specified in the CREATE INDEX statement. Each of these columns displays a value of zero ( 0 ) if the corresponding EXTENT SIZE or NEXT SIZE clause was omitted when the index was created.

If the CREATE INDEX statement that defines a new index includes no explicit extent size specifications, the database server automatically calculates the first and next extent sizes, but the fextsize and nextsize column values are set to 0. When the database server is converted from a release earlier than Version 11.70, the fextsize and nextsize values for every migrated index are 0.

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