Oracle Index Information

Some queries to help find out infomration about specific indexes.

This will show you the indexes for a specified table

select	i.index_name
,	i.tablespace_name
,	ceil(s.bytes / 1048576) "Size MB"
from 	dba_indexes i
,	dba_segments s
where 	i.index_name = s.segment_name
and 	table_name like '&table'
order 	by 2, 1

This will show you the columns of a specific index

select 	column_name
from 	dba_ind_columns
where 	index_name = '&index'
order 	by column_position 

This will give you some extra details about the index

select index_name, num_rows, last_analyzed from dba_ind_statistics where table_name = 'Table Name';