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
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' 2
/

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




select dbms_metadata.get_ddl('TABLE','USER_SITES','ANUJ') from dual;

DBMS_METADATA.GET_DDL('TABLE','USER_SITES','ANUJ')
--------------------------------------------------------------------------------

CREATE TABLE "ANUJ"."USER_SITES"
( "USER_OBJECT_ID" VARCHAR2(16) NOT NULL ENABLE,
"SITE_NO" VARCHAR2(12) NOT NULL ENABLE,
"SITE_DATE_ADDED" DATE NOT NULL ENABLE,
"TANDC_DATE_ACCEPTED" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "ANUJ" ;
ALTER TABLE "ANUJ"."USER_SITES" ADD CONSTRAINT "USER_SITES_USPK1" PRIMARY
KEY ("USER_OBJECT_ID", "SITE_NO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "ANUJ" ENABLE;






SQL> create table cccprod1.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 "CCCPROD1"."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
-- index_name in ('USER_SITES_USPK1')
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 <<<<---- good Clustering Factor

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