Menu
How to find all indexes available on table in db2?
257
To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA. STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you all indexes in all schemas.
INDEX is an ordered set of pointers to the rows of table data. DB2 uses indexes to uniqueness and improve the performance. An index is stored separately from the data in table. Index's are physically stored in its index space.
Any number of indexes can be defined on a particular table, to a maximum of 32 767, and they can have a beneficial effect on the performance of queries. The index manager must maintain the indexes during update, delete and insert operations.
There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
db2 "select * from syscat.indexes where tabname = 'your table name here' \
and tabschema = 'your schema name here'"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
