Here’s a quck and dirty I use occasionally to check the definitions and statistics of all the indexes on a single table. I’ve probably left a couple of hundred versions of it scattered around the world including or excluding various bits of information – I know that I don’t often include dba_ind_expressions.
You might want to put in some of your own formatting commands, and write versions that cater for partitions and sub-partitions. (If you want it all in one, a simple UNION ALL approach is the easiest thing to do).
The code picks out a few things like uniqueness of index, nullability of columns, descending columns in indexes, and various index and column stats. It’s really aimed at B-tree indexes – IOTs and bitmap indexes merit a slightly different piece of SQL:
set pagesize 60 set linesize 180 column index_name format a20 column column_name format a20 column func format a24 break on index_name skip 1 on uniqueness on pref_len - on t_rows on i_rows on distinct_keys on blevel on leaf_blocks select ind.index_name, ind.uniqueness, ind.prefix_length pref_len, tab.num_rows t_rows, ind.num_rows i_rows, ind.distinct_keys, ind.blevel, ind.leaf_blocks, inc.column_name, case inc.descend when 'DESC' then 'D' else null end descend, tbc.nullable, tbc.num_distinct, tbc.num_nulls, inx.column_expression func from dba_tables tab, dba_indexes ind, dba_ind_columns inc, dba_tab_cols tbc, dba_ind_expressions inx where tab.owner = 'XXX' and tab.table_name = 'YYYYYYYY' /* */ and ind.table_owner = tab.owner and ind.table_name = tab.table_name /* */ and inc.table_owner = ind.table_owner and inc.table_name = ind.table_name and inc.index_owner = ind.owner and inc.index_name = ind.index_name /* */ and tbc.owner = inc.table_owner and tbc.table_name = inc.table_name and tbc.column_name = inc.column_name /* */ and inx.table_owner(+) = inc.table_owner and inx.table_name(+) = inc.table_name and inx.index_owner(+) = inc.index_owner and inx.index_name(+) = inc.index_name and inx.column_position(+) = inc.column_position order by ind.index_name, inc.column_position ;
Update Feb 2010: Just as one very simple idea of what you can look for in this report – if the number of distinct keys in the index is a lot smaller than the number of rows in the index then the index is likely to be a good candidate for compression.
As a secondary check, if the number of distinct values of the first column is small relative to the number of rows, then compression on the first column might be a good option; similarly, if the product of the number of distinct values in the first “N” columns is small compared to the number of rows the index is still a good candidate for compression on “N” columns.