Index Definitions

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.