Index Clustering Factor and Oracle

Clustering factor is the relationship between the order of the table and the order of the index. Indexes are always stored in key value sequence. Tables are stored in the order of the inserts…generally. There are a few more factors that might alter this but for the most part, inserts are added to the end of the table.

So, if you have a primary key index and a sequence generator for this said primary key, this index and the table will be in the same order.

Clustering factor comes into play with Range Scan operations when executing your SQL (review explain plans and the index access to see if you are performing a range scan…). At any rate, this is a common way to process your SQL using indexes when you are asking for:

• Repeating values
• Greater than
• Between

You can run this report in Toad if you like.

Email me and ask for my Index_Info.sql report. This report shows you a variety of stats on your indexes. The clustering factor will play a big part on whether Oracle chooses to use the index or not.

The higher the clustering factor, the more the index points to different data blocks. Indexes are efficient when they point to just a few of the table data blocks…

Oracle has always had this 20% factor…in that…if more than 20% of the table is going to be accessed, Oracle will probably do a full-table scan operation on the table rather than using an index.

Notice the A B and C tables in the red circle above. I did the math for you on the clustering factor giving this Index Quality column!  …a clever use of newer SQL syntax…the inline Case statement …anyway…  This Index Quality is based on < 5% clustering factor (divide clustering factor by number of rows)…is Excellent, < 10% is Good, < 15% is Fair, and anything > 20% is Poor. I want to add the actual percentage sometime…because Oracle could use the index on a ‘poor’ rating if the clustering factor percent is close to 20%…depending of course, what you are asking for in the WHERE clause.

So…the indexes in the B table will always be used. The A_STATUS_IND index on the A table will be used. The other index on the A table and the index on the C table will never be used. These become wasted space and more importantly…wasted time for Oracle to maintain on any DML operation!!!  Also take notice of some of the indexes on the MASTER table. Some will be considered, others will never be used.

Clustering factor is not used on individual row lookups…just on range scans…

I have a fun scenario where I have a great working example, when you mix this with my Index Monitoring scripts…perhaps an article soon!