Each row in the SYSIDX system view defines a logical index in the database. The underlying system table for this view is ISYSIDX.
| Column name | Data type | Description |
|---|---|---|
| table_id | UNSIGNED INT | Uniquely identifies the table to which this index applies. |
| index_id | UNSIGNED INT | A unique number identifying the index within its table. |
| object_id | UNSIGNED BIGINT | The internal ID for the index, uniquely identifying it in the database. |
| phys_index_id | UNSIGNED INT | Identifies the underlying physical index used to implement the logical index. This value is NULL for indexes on temporary tables or remote tables. Otherwise, the value corresponds to the object_id of a physical index in the SYSPHYSIDX system view. |
| dbspace_id | SMALLINT | The ID of the file in which the index is contained. This value corresponds to an entry in the SYSDBSPACE system view. |
| file_id | SMALLINT | DEPRECATED. This column is present in SYSVIEW, but not in the underlying system table ISYSIDX. The contents of this column is the same as dbspace_id and is provided for compatibility. Use dbspace_id instead. |
| index_category | TINYINT |
The type of index. Values include:
|
| "unique" | TINYINT | Indicates whether the index is a unique index (1), a non-unique index (4), or a unique constraint (2). A unique index prevents two rows in the indexed table from having the same values in the index columns. |
| index_name | CHAR(128) | The name of the index. |
| not_enforced | CHAR(1) | For system use only. |
| file_id | SMALLINT | For system use only. |
PRIMARY KEY (table_id, index_id)
FOREIGN KEY (table_id) references SYS.ISYSTAB (table_id)
FOREIGN KEY (object_id) references SYS.ISYSOBJECT (object_id) MATCH UNIQUE FULL
FOREIGN KEY (table_id, phys_index_id) references SYS.ISYSPHYSIDX (table_id, phys_index_id)
UNIQUE Index (index_name, table_id, index_category)