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