Indexes on any table –

This is a small query I always keep handy, given the table name as input, it lists down 
all the Indexes and its constituent column(s).


WITH indx AS(
	SELECT
		index_owner,
		index_name,
		table_name,
		LISTAGG(column_name, ', ') WITHIN GROUP (
			ORDER BY column_position ASC
		) lst 
	FROM
		all_ind_columns
	WHERE
		table_name IN ('TABLE_1','TABLE_2') -->>-- input: Table name(s)
	GROUP BY
		index_owner,
		index_name,
		table_name
)
SELECT
	x.*,
	DECODE(REGEXP_COUNT(x.lst,','),
		0,'Normal Index',
		'Composite Index'
	) idx_type -->>-- identifies the Index is a Normal or Composite index
FROM indx x
ORDER BY x.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

%d bloggers like this: