Index Quality in Oracle Databases

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

One comment

  1. <= 6 or <7 would do a better job.

Leave a Reply

Your email address will not be published. Required fields are marked *