Clustering Factor and its Effects on Performance

Clustering Factor :

During table blocks reading using index how many switches took place between table blocks is counted as clustering factor.Simply can say If values in the leaf block are in different blocks in the table, then the clustering factor is incremented by one.

 i.e if a given index leaf block have five entires and in tables these entires are separated over 4 blocks then block switches are
four.and if exists in single block then clustering factor is 1.

Effects on performance :

CBO calculate the costing on the basis of the clustering factor hence its directly influence the performance.


If clustering rector is poor :-

1)  More data blocks read More disk I/O
2)  Tables are flush quickly from the buffer pool because a relatively large number
     of blocks has to be read in buffer pool

How can Reduce the Clustering factor:- 

In below example shown if the index is created on pre-sorted value the clustering factor can be reduce hugely:


SQL> create table test_tables as select * from dba_tables;

Table created.

SQL> create index test_index on test_tables(table_name);

Index created.

We can calculate the clustring fector from  clustering_factor.user_indexes 


SQL>  select clustering_factor from user_indexes where index_name='TEST_INDEX'

CLUSTERING_FACTOR
-----------------
654

Check the number of rows in table

SQL> select count(*) from test_tables;

COUNT(*)
----------
1585

Find the rows are distributed in houw many blocks


SQL>  select blocks from user_segments where segment_name='TEST_TABLES' 

BLOCKS
----------
56

In above example 1585 rows are distributed in 56 blocks and clustering factor is 654.

Now we insert the data in sorted form in the column in which we have to create the index

SQL> create table test_tables_o as select * from dba_tables order by table_name;

Table created.

SQL> create index test_index_o on test_tables_o(table_name);

Index created.

Calculate the clustering factor it becomes 48 and number of rows and blocks are same

SQL> select clustering_factor from user_indexes where index_name='TEST_INDEX_O';

CLUSTERING_FACTOR
-----------------
48

SQL> select count(*) from test_tables_o;

COUNT(*)
----------
1586

SQL> select blocks from user_segments where segment_name='TEST_TABLES';

BLOCKS
----------
56