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 HCL 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.