What is a Clustering Factor and how to improve?

Clustering Factor is a value that tells Oracle how the rows in a table are randomly distributed with respect to
index key values. good Clustering Factor value would be

dba_indexes.CLUSTERING_FACTOR=dba_tables.blocks


if difference is very high than cost of using index will be high

how to improve Clustering Factor ?

set linesize 200
select a.index_name,b.table_name,b.num_rows,b.blocks,a.clustering_factor
from dba_indexes a, dba_tables b
where 1=1
and a.table_name = b.table_name
and b.owner='ANUJ'
and b.table_name='USER_SITES'

INDEX_NAME TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 USER_SITES 1038392 6544 570792


in this case Clustering Factor is very bad .


get index info from following query

set long 50000
select dbms_metadata.get_ddl('INDEX','USER_SITES_USPK1','ANUJ') from dual ;


SELECT constraint_name,table_name,r_constraint_name,status FROM dba_constraints 
WHERE constraint_type='P' and table_name='USER_SITES' 
/

CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
USER_SITES_USPK1 USER_SITES ENABLED



SQL> create table anuj.user_sites_new as select * from anuj.user_sites order by USER_OBJECT_ID,SITE_NO ;

Table created.

SQL> select count(*) from anuj.user_sites;

COUNT(*)
----------
1043588

SQL> select count(*) from anuj.user_sites_NEW;

COUNT(*)
----------
1043588

SQL> CREATE UNIQUE INDEX "ANUJ"."USER_SITES_USPK_NEW" ON "ANUJ"."USER_SITES_NEW" ("USER_OBJECT_ID", "SITE_NO")
TABLESPACE "ANUJ"; 

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'ANUJ',-
tabname=>'USER_SITES_NEW',-
estimate_percent => 100,-
cascade=>true,-
degree => DBMS_STATS.AUTO_DEGREE,-
method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from dba_indexes a, dba_tables b
where 1=1
-- and index_name in ('USER_SITES_USPK1')
and a.table_name = b.table_name
and b.table_name in ('USER_SITES','USER_SITES_NEW') 
/


INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 1038392 6544 570792 <<<<<--- 
USER_SITES_USPK_NEW 1043588 6596 6586 <<<<<--new Clustering Factor 


now drop the old table. rename new table to old table name


to modify clustering factor >12c

TABLE_CACHED_BLOCKS statistics preference allows us to modify how the CF is 
calculated by not incrementing the CF value if an index rowid points to a block that was visited 
just TABLE_CACHED_BLOCKS




set linesize 100 pagesize 300

define tabname='EMP'
define owner='ANUJ'
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual 





PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
TABLE_CACHED_BLOCKS :                             1




to set value !!!

set linesize 100 pagesize 300

define tabname='EMP'
define owner='ANUJ'
exec dbms_stats.set_table_prefs(ownname=>'&&owner', tabname=>'&&TABLE', pname=>'TABLE_CACHED_BLOCKS', pvalue=>10);
 



define tabname='EMP'
define owner='ANUJ'
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual

SQL> /
old   1: select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual
new   1: select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'EMP',ownname => 'ANUJ') prefs_for__EMP FROM dual

PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
TABLE_CACHED_BLOCKS :                             10


EXEC dbms_stats.gather_table_stats(ownname=>&&owner, tabname=>'&&TABLE', estimate_percent=> null, cascade=> true,   method_opt=>'FOR ALL COLUMNS SIZE 1');


gather stats ... 

define tabname='EMP'
define owner='ANUJ'

set pages 100 lines 250
--set echo off feedback off heading on 
col gather for a200
spool gather.sql
select 'SET ECHO ON FEEDBACK ON TIMING ON' FROM DUAL;
select 'exec dbms_stats.gather_table_stats (ownname => ''' || owner ||''', tabname => '''||table_name||''' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => '''||' FOR ALL COLUMNS SIZE AUTO'''||' , degree => 8);' gather  from   dba_tables
where table_name in ('&&tabname')
and OWNER='&&owner'
;



exec dbms_stats.gather_table_stats (ownname => 'ANUJ', tabname => 'EMP' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8);