Cardinality of the Attributes of an Index Affect Performance

Indexes that have attributes with only low cardinality (few values) can be major performance problems. Another article and script, "Performance of Database Indexes", has a more detailed explanation and similar script for finding problem 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 ######################