What Is Index Clustering Factor?

Have you heard other DBAs talking about the clustering factor of an index, and saying that if the clustering factor is a certain value or percentage, then do a specific action?

Did you pretend you knew what they are talking about, and agreed in silence with the action plan, because to be honest, you had no clue of what clustering factor even meant?

This happens to the best of us!

Remember, at one point, everyone was a beginner, and nobody knew what the clustering factor was, until one day, they heard and learned about it.

Also, maybe at one time, you knew what clustering factor meant, and you have forgotten, because you are not using the terminology on a daily basis.

So if you have no clue of what clustering factor is, or if you have forgotten about it, then this post is for you. Keep reading.

What is the clustering factor?

The clustering factor is a statistic, used by the optimizer to determine how efficient and index is.
The clustering factor tells you the number of blocks that will be read when scanning the index. In other words, how many I/O operations would be performed if every row in the table would be read by using the index, following the index order.

If the rows in the table on disk are sorted in the same order as the index, there will be minimum number of I/O operations to read every row from the table via the index.
Why is that?
Because the next row in the index will most likely be the next row in the table, and there will be no jumping around from block to block, to find the next row.
Thus, the clustering factor will be lower, and will be close to the number of blocks in the table. In this case the index will be useful for large index range scans, returning large number of rows.

If the data in the table is scattered and is not in the same order as the index, then there will be more I/O operations required to read every row from the table via the index.
Thus the clustering factor will be higher, closer to the number of rows in the table. In this case the index will not be useful to return large number of rows from the table.

Remember that a table could have multiple indexes, and each index could have a different clustering factor. Some indexes could have a low clustering factor, and some indexes could have a large clustering factor.

To find out what the clustering factor for an index is, check out the column CLUSTERING_FACTOR in DBA_INDEXES view.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana