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.
o.bytes / 1048576 mb,
WHEN NVL (i.clustering_factor, 0) = 0
WHEN NVL (t.num_rows, 0) = 0
WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) < 6
WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 7
WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 12
WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 16
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,
<= 6 or <7 would do a better job.