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 ######################



The views and opinions expressed in this page are strictly those of the page author.
The contents of this page have not been reviewed or approved by the University of Minnesota.