Recipe 5.3. Listing Indexed Columns for a TableProblemYou want list indexes, their columns, and the column position (if available) in the index for a given table. SolutionThe vendor-specific solutions that follow all assume that you are listing indexes for the table EMP in the SMEAGOL schema. DB2Query SYSCAT.INDEXES: 1 select a.tabname, b.indname, b.colname, b.colseq 2 from syscat.indexes a, 3 syscat.indexcoluse b 3 where a.tabname = 'EMP' 4 and a.tabschema = 'SMEAGOL' 5 and a.indschema = b.indschema 6 and a.indname = b.indname OracleQuery SYS.ALL_IND_COLUMNS: select table_name, index_name, column_name, column_position from sys.all_ind_columns where table_name = 'EMP' and table_owner = 'SMEAGOL' PostgreSQLQuery PG_CATALOG.PG_INDEXES and INFORMATION_SCHEMA.COLUMNS: 1 select a.tablename,a.indexname,b.column_name 2 from pg_catalog.pg_indexes a, 3 information_schema.columns b 4 where a.schemaname = 'SMEAGOL' 5 and a.tablename = b.table_name MySQLUse the SHOW INDEX command: show index from emp SQL ServerQuery SYS.TABLES, SYS.INDEXES, SYS.INDEX_COLUMNS, and SYS.COLUMNS: 1 select a.name table_name, 2 b.name index_name, 3 d.name column_name, 4 c.index_column_id 5 from sys.tables a, 6 sys.indexes b, 7 sys.index_columns c, 8 sys.columns d 9 where a.object_id = b.object_id 10 and b.object_id = c.object_id 11 and b.index_id = c.index_id 12 and c.object_id = d.object_id 13 and c.column_id = d.column_id 14 and a.name = 'EMP' DiscussionWhen it comes to queries, it's important to know what columns are/aren't indexed. Indexes can provide good performance for queries against columns that are frequently used in filters and that are fairly selective. Indexes are also useful when joining between tables. By knowing what columns are indexed, you are already one step ahead of performance problems if they should occur. Additionally, you might want to find information about the indexes themselves: how many levels deep they are, how many distinct keys, how many leaf blocks, and so forth. Such information is also available from the views/tables queried in this recipe's solutions. |