SYSINDEXES

The sysindexes table is a view on the sysindices table. It contains one row for each index in the database.

The sysindexes table has the following columns.
Table 1. SYSINDEXES table column descriptions
Column Type Explanation
idxname VARCHAR(128) Index name
owner VARCHAR(32) 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
  • G = Nonbitmap generalized-key index
  • g = Bitmap generalized-key index
  • u = unique, bitmap
  • d = nonunique, bitmap
clustered CHAR(1) Clustered or nonclustered index (C = Clustered)
part1 SMALLINT Column number (colno) of a single index or the 1st component of a composite index
part2 SMALLINT 2nd component of a composite index
part3 SMALLINT 3rd component of a composite index
part4 SMALLINT 4th component of a composite index
part5 SMALLINT 5th component of a composite index
part6 SMALLINT 6th component of a composite index
part7 SMALLINT 7th component of a composite index
part8 SMALLINT 8th component of a composite index
part9 SMALLINT 9th component of a composite index
part10 SMALLINT 10th component of a composite index
part11 SMALLINT 11th component of a composite index
part12 SMALLINT 12th component of a composite index
part13 SMALLINT 13th component of a composite index
part14 SMALLINT 14th component of a composite index
part15 SMALLINT 15th component of a composite index
part16 SMALLINT 16th component of a composite index
levels SMALLINT Number of B-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
idxflags INTEGER Bitmap storing the current locking mode of the index

As with most system catalog tables, changes that affect existing indexes are reflected in this table only after you run the UPDATE STATISTICS statement.

Each part1 through part16 column in this table holds the column number (colno) of one of the 16 possible parts of a composite index. If the component is ordered in descending order, the colno is entered as a negative value. The columns are filled in for B-tree indexes that do not use user-defined data types or functional indexes. For generic B-trees and all other access methods, the part1 through part16 columns all contain zeros.

The clust column is blank until the UPDATE STATISTICS statement is run on the table. The maximum value is the number of rows in the table, and the minimum value is the number of data pages in the table.