Find the Order and Cardinality of Attributes in a Database Index

Sometimes the index picked is a dog and just does not work very well. Most indexes are supposed to contain attributes that have a large number of values evenly spread over the domain of attribute values. This allows the optimizer to get the fastest search plan for the retrieval of data. Well, sometimes the index attributes have very few values and are not good. This script helps to show the order and cardinality of the attributes in an index so the DBA can assess whether this index is worth the overhead. This script is dependent on runing the analyze utilities to load statistics for the SQL optimizer.

On some small tables, who cares, you do not even need an index. On other large tables an index will make a huge difference. This script helps me decide if the index will be useful. Why create an index if there is only a couple values in the domains of the attributes chosen, it will just add overhead to most queries.

#!/bin/ksh

# Find order and cardinality of attributes in an index.
# Use to find if an index has good attributes.

# Note: Dependent on running "analyze" to load statistics for
#       the optimizer.
#
# Usage example: index_order.ksh APPOWNER TABLEX_INDEX1 TABLEX

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

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

test $# -eq 3 || usage

SCHEMA_OWNER=$1
export SCHEMA_OWNER="'"$SCHEMA_OWNER"'"
INDEX_NAME=$2
export INDEX_NAME="'"$INDEX_NAME"'"
TABLE_NAME=$3
export TABLE_NAME="'"$TABLE_NAME"'"

sqlplus -s << EOF
system/secretpassword

column index_name format a20
column column_name format a8
column column_position format 9999

select i.index_name, i.column_name , t.num_distinct, i.column_position
from
   dba_ind_columns i
  ,dba_tab_columns t
where i.table_owner = $SCHEMA_OWNER
and t.owner = $SCHEMA_OWNER
and i.index_name=$INDEX_NAME
and t.table_name=$TABLE_NAME
and i.table_name=$TABLE_NAME
and i.column_name = t.column_name
order by i.column_position;

EOF