Monday, August 13, 2012

How to interpretate the clustering factor of an index

  • Vital statistic used by CBO to determine cost of index access
  • Clustering factor value corresponds to likely physical I/0s or blocks visited during a full index scan (note same block could be visited many times)
  • If the same block is read consecutively then Oracle assumes only the 1 physical I/0 is necessary
  • Determines the relative order of the table in relation to the index
  • The better the Clustering factor, the more efficient the access via the corresponding index as less physical I/Os are likely
  • “Good” Clustering factor generally has value closer to blocks in table
  • “Bad” Clustering factor generally has a value closer to rows in table
  • Generaly the clustering factor will be between the number of blocks and the number of rows in a table
  • A low number is good and reflect strong clustering
  • A high number is bad and reflect weak clustering
  • The clustering may be lower than the number of blocks if there are empty blocks in the table below the HWM and/or there are many rows that have null for the indexed columns
  • The clustering factor can never be greater than the number of rows
  • The clustering factor for a bitmap index is just a copy of the number of rows and is never used

No comments:

Post a Comment