Help: Check Performance of a Database Index

Most database 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 the indexed attributes of the data in the query.

If the index attributes have very few values the index may not be very good. This script helps to show the attribute order in the index Data Definition Language and cardinality (how many values) of the attributes in an index so the DBA can assess whether an index may or may not be helping performance.
An example is a table of a billion rows with an index, "Bad_Index", defined on an attribute that has only two values, "true" and "false". The index will probably not be very useful. A query of the same table of a billion records with an index, "Imp_Index", defined on an attribute that has a hundred million values will probably be magnitudes of ten faster than a query using only the "Bad_Index".
On large tables a good index will make a huge difference in performance of the queries using it. I use this script to help me decide if the index will be useful, if it needs to be modified to use different attributes or if the index is useless and should be dropped.

#!/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
}

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