Monday, April 16, 2012

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';


Post a Comment