Index Tuning

Oracle indexes can be tuned looking to three main characteristics:
SELECTIVITY: you can calculate index selectivity using this formula:
SELECT DISTINCT_KEYS/NUM_ROWS SELECTIVITY
FROM DBA_INDEXES
/
Values range from 0 to 1, where 1 is the optimal value (UNIQUE indexes and PK), while with values near to 0 performance is lower
CLUSTERING FACTOR: measures how many keys are stored in a single index block.
This value is stored in DBA_INDEXES.CLUSTERING_FACTOR column.
If it’s near to DBA_INDEXES_BLOCKS value then the index is good, if instead it’s near to DBA_INDEXES.NUM_ROWS performance is lower
BLEVEL: Oracle indexes are stored in a B-TREE structure where leaves contain the actual index keys.
The DBA_INDEXES.BLEVEL column shows how many branch-blocks you have to navigate in order to reach the index keys.
If BLEVEL should not be greater to 5.
There are two main methods you can use to optimize indexes:
1) REBUILD index or index partitions if you want to lower BLEVEL values
2) Copy and re-create the table using CREATE TABLE AS SELECT (CTAS) with ORDER BY clause on index columns, if you want to optimize the CLUSTERING FACTOR
You should also consider different INDEX types if you want to optimize index SELECTIVITY:
BITMAP INDEXES are the best for low SELECTIVITY columns, expecially if the table is substancially static, because they show low performance for DML operations.
INDEX-ORGANIZED TABLES (IOTs) combine table and index structures reducing the average I/O: best for lookup tables with small average row length.
FUNCTION_BASED INDEXES are not directly based on column values, but on derived values computed via standard or user-defined functions.