Index quality in Oracle databases

Index quality is a factor that directly affects the overall database performance. The quality of an index is measured by the clustering_factor/num_rows ratio. This ratio is directly related to the index’s usage in the query. If the index quality is poor, the query will cause a full table scan.

The clustering factor is a measure of the order of an index relative to the underlying table. It is used to control the cost of a table lookup following an index access. The clustering factor records the number of blocks to be read when scanning the index. The clustering factor value in the CLUSTERING_FACTOR column in the DBA_INDEXES, USER_INDEXES, and ALL_INDEXES views is determined by the following operations.

• The index is scanned sequentially.
• The block portion of ROWID pointed to by the current indexed value is compared with the previously indexed value.
• If the ROWIDs point to different table blocks, the clustering factor value is increased.

If the index quality is poor, such as when it is fragmented, it should be rebuilt; otherwise, the index should be dropped and query performance monitored.

We can determine the quality of indexes in a schema using the following query :

SELECT i.table_name,
         t.num_rows,
         t.blocks,
         i.index_name,
         o.bytes / 1048576 mb,
         i.avg_data_blocks_per_key,
         i.avg_leaf_blocks_per_key,
         i.clustering_factor,
         CASE
            WHEN NVL (i.clustering_factor, 0) = 0
            THEN
               '0 - no statistics'
            WHEN NVL (t.num_rows, 0) = 0
            THEN
               '0 - no statistics'
            WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) < 6
            THEN
               '5-star rating'
            WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 7
                                                                      AND 11
            THEN
               '4 - very good'
            WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 12
                                                                      AND 15
            THEN
               '2-good'
            WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 16
                                                                      AND 25
            THEN
               '2-Medium'
            ELSE
               '1-weak'
         END
            index_quality
    FROM dba_indexes i, dba_segments o, dba_tables t
   WHERE i.owner = t.owner
         AND i.table_name = t.table_name
         AND i.owner = o.owner
         AND i.index_name = o.segment_name
         AND t.owner = UPPER ('<SCHEMA_NAME>')
ORDER BY table_name,
         num_rows,
         blocks,
         index_quality DESC;

Query results may be as below :

Depending on the quality of the indexes, rebuilding or dropping the database can positively improve overall performance.