Columns and usage of indexes
The following query
joins dba_indexes with
dba_ind_columns and
dba_index_usage to select all
indexes on a
table, the respective indexed columns and index usage (if enabled):
select
-- ic.table_name,
case when ic.column_position = 1 then ix.index_name end index_name,
ic.column_name,
case when ic.column_position = 1 then ix.index_type end index_type,
case when ic.column_position = 1 then ix.table_type end table_type,
case when ic.column_position = 1 then iu.last_used end last_used,
case when ic.column_position = 1 then iu.total_access_count end total_access_count,
ic.column_position
from
dba_indexes ix join
dba_ind_columns ic on ix.owner = ic.index_owner and
ix.index_name = ic.index_name left join
dba_index_usage iu on ix.owner = iu.owner and
ix.index_name = iu.name
where
ix.owner = '...' and
ix.table_name = '...'
order by
ix.index_name,
ic.column_position;