Look at the Cardinality of the Attributes of an Index

Although the index should be like a table, that attribute order should not matter, The indexes that have attributes with low cardinality (few values) can be major performance problems and can be fixed by adding attributes with a large well distributed set of values.

Note: This is dependent on running "analyze" to load statistics for the SQL optimizer as the statistics are queried to find the lame indexes.

#!/bin/ksh
# http://www.sofbot.com/   Steven Hauser & Associates.
# Find the indexes that have the attributes with
# low cardinality (few values).  These indexes can be 
# major performance problems and can sometimes be easily fixed by 
# adding attributes with a large well distributed set of values.
#
# Note: Dependent on running "analyze" to load statistics for
#       the optimizer.
#
# Usage example: index_check.ksh APPOWNER 5 10000

# Usage message.
usage () {
    echo;
    echo 'Usage: '$0 ' '
    echo;
    exit 1
}

###################################
# Main

test $# -eq 3 || usage

SCHEMA_OWNER=$1
export SCHEMA_OWNER="'"$SCHEMA_OWNER"'"
export CARDINALITY=$2
export ROWS=$3

sqlplus << EOF
system/manager

select i.index_name, i.column_name , t.num_distinct
from dba_ind_columns i, dba_tab_columns t, dba_tables tt
where i.table_owner = $SCHEMA_OWNER
and i.column_position = 1
and t.num_distinct < $CARDINALITY
and tt.num_rows > $ROWS
and i.column_name = t.column_name
and i.table_name = tt.table_name
and i.table_name = t.table_name
and i.table_owner = tt.owner
and t.owner = i.table_owner;

EOF
#################### cut here ######################