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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s