The value of the clustering_factor affects Oracle's choice of execution plan -
but the clustering_factor has always been derived using an unrealistic
algorithm that usually makes it much higher than it should be, which is why the
optimizer can easily pick the wrong index, or decide to use a tablescan, or
choose a bad join order when your testing shows that a particular index to
access a particular table in a particular join order is clearly a good idea.
Changing the clustering_factor (number stored in the database) can help the
optimizer choose the right path. Changing the physical arrangement of the data
in the database to achieve the change in value is a bad idea.
Historically we could simple use dbms_stats.set_index_stats() to change the
value stored for a given index, but in 11.2.0.4 (backported from 12c) Oracle
introduced a method of making the clustering_factor algorithm produce a more
realistic result.
Set the table preference 'table_cached_blocks' to a value like 16 (or 16 * node
count, for RAC, or "realistic degree of concurrency of inserts" for highly
concurrent systems) and gather index stats and you will find that the
clustering_factor for indexes on columns that show "weak" clustering will drop
significantly.
See:
https://richardfoote.wordpress.com/2013/05/08/important-clustering-factor-calculation-improvement-fix-you/
(the first of 3 posts), and
http://jonathanlewis.wordpress.com/?s=table_cached_blocks<https://jonathanlewis.wordpress.com/?s=table_cached_blocks>
I'm going to take a look at the note you quoted and point out the omission.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of sumit Tyagi [dba.tyagisumit@xxxxxxxxx]
Sent: 04 June 2016 08:01
To: Oracle L
Subject: How to Reduce Index clustering Factor without downtime
Hi Oracle-L team ,
How can Clustering Factor be Reduced : My Question is how to reduce CF in a
production OLTP environment where table data is constantly changing and we
can't afford a downtime .
Clustering Factor (Doc ID 39836.1)
The only method to affect the clustering factor is to sort and then store the
rows in the table in the same order as in they appear in the index. Exporting
rows and putting them back in the same order that they appeared originally will
have no affect. Remember that ordering the rows to suit one index may have
detrimental effects on the choice of other indexes.
--
--
BR
Sumit Tyagi
+91-7829543355
