Index Quality in Oracle Databases

dbtut

5 years ago

Index quality is a factor that directly affects all database performance. The quality of an index is measured by the rate of clustering_factor / num_rows. This ratio is directly related to the use of the index. If the index quality is poor, it will cause a full table scan.

The Clustering factor is a measure of the order of an index relative to the table. The Clustering factor records the number of blocks to 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.

Index is scanned in order
The block portion of ROWID, which is indicated by the current indexed value, is compared with the previous indexed value.
If ROWIDs point to different table blocks, the clustering factor value is increased.

If the quality of the index is poor, rebuild is required if the index is fragmented. In other cases it is necessary to drop the relevant index and observe the performance of the query.

Evaluate Index Quality

We can learn the quality of indexes in a schema with 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-wonderful'
            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-not bad'
            ELSE
               '1-bad'
         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 ('&YOUR_USER_NAME')
ORDER BY table_name,
         num_rows,
         blocks,
         index_quality DESC;

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-wonderful'

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-not bad'

ELSE

'1-bad'

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 ('&YOUR_USER_NAME')

ORDER BY table_name,

num_rows,

blocks,

index_quality DESC;