Here you will learn how to list all indexes of an Ingres database, all indexes of a specific table, or whether a certain field is included in an index.
The indexes of an Actian Ingres database can be displayed using simple SELECT queries.
To display all existing indexes of all tables, you can simply query the iiindexes table:
SELECT * FROM iiindexes
However, you often only want to see the indexes of a particular table. To do this, add an appropriate WHERE condition to your SQL statement. (The field iiindexes.base_name contains the table name). Simply replace tablename with the desired table name:
SELECT * FROM iiindexes WHERE base_name = 'tablename'
The previous queries list only the names of the corresponding indexes – not the fields/columns assigned to the index. To display all columns of an index, query the table iiindex_columns. Here, index_name is a value from the column iiindex.index_name from the query of the iiindexes table:
SELECT * FROM iiindex_columns WHERE index_name = 'indexname'
The following query provides you with all indexes of a specific table including the corresponding columns/fields:
SELECT * FROM iiindexes LEFT JOIN iiindex_columns ON iiindexes.index_name = iiindex_columns.index_name WHERE base_name = 'tablename' ORDER BY iiindexes.index_name, key_sequence
If you want to know whether a specific table field is already included in an index, it is best to use this option:
SELECT * FROM iiindexes LEFT JOIN iiindex_columns ON iiindexes.index_name = iiindex_columns.index_name WHERE base_name = 'tablename' and column_name = 'columnname'
