Look at the Cardinality of the Attributes of an Index

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 low cardinality attributes in the 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 ######################