B-tree indices in Oracle are powerful. Sometimes a very good index can be even better. Precise for Oracle helps DBAs recognize this situation.
This screen excerpt (please click on it to enlarge) from Precise for Oracle identifies that 20% of execution time is spent accessing the index TD_FN_X1 in a Heavy Index Range Scan. This SQL statement sums AMOUNT for similar FIRST_NAMEs.
Checking on the health metrics on this index, a very high clustering factor is identified. This means that when this index is used in a range scan of numerous rows, additional operations will be required to pull back the AMOUNT column from the base table: CPU and I/Os are consumed. This is inefficient.
The SQL statement filters on FIRST_NAME in the WHERE clause and sums AMOUNT. A covering index was created. The Amount column was added to the index so that TD_FN_X1 is now comprised of the columns FIRST_NAME and AMOUNT. By including AMOUNT in the index, I/Os are avoided circling from the index back to the underlying table to bring in the AMOUNT column.
This change cut execution time half improving application performance and minimizing server resource consumption.
Thanks to DP for sharing this Oracle blog post Building Test Scenario.
More info on the Index Health Metric Clustering Factor Bad Index – use Clustering Factor to determine when a range scan is expensive