500 rows in a teeny tiny table like that isn't a good test case.

num rows and num blocks are far far too "wacky"



the clustering factor is a measure of the number of blocks that would be read (LIO wise) if you range scanned the index -> table from beginning to end.

but really, the clustering factor would be tiny here -- both should have tbeen the "same" -- 1.  this is a one block table!

ops$tkyte@ORA9IR2> edit
Wrote file afiedt.buf
 
  1  begin
  2  for i in 1..500
  3  loop
  4      insert into t values(i, i+i-(i*3));
  5  end loop;
  6* end;
ops$tkyte@ORA9IR2> /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> create index t_c1 on t(c1);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_c2 on t(c2);
 
Index created.
 
ops$tkyte@ORA9IR2> select num_rows from user_tables where table_name = 'T';
 
  NUM_ROWS
----------
 
 
ops$tkyte@ORA9IR2> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2> select num_rows from user_tables where table_name = 'T';
 
  NUM_ROWS
----------
       500
 
ops$tkyte@ORA9IR2> select blocks from user_tables where table_name = 'T';
 
    BLOCKS
----------
         5
 
ops$tkyte@ORA9IR2> select clustering_factor from user_indexes where table_name= 'T';
 
CLUSTERING_FACTOR
-----------------
                1
                1
 
ops$tkyte@ORA9IR2> select dbms_rowid.rowid_block_number(rowid), count(*) from t
  2  group by  dbms_rowid.rowid_block_number(rowid);
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                 246        500
 

 

don't do that.  not what they were designed for.  makes your database harder to manage.


the cluster factor is a measure of how sorted the table is with respect to the index key.  it is a measure of how many consistent gets would have to be performed to read the entire table via the index -- eg: 

create table t ( x int, y int );
alter table t add constraint t_pk primary key(x);

select /*+ index( t t_pk ) */ y from t;


so we would 

  index range scan -> table access by index rowid;

how many LIO's did we need to do that (on the table) with an array size of "ALL ROWS".  that is what the cluster factor would be.

if the table is sorted by the primary key -- the cluster factor would equal the number of blocks.  if the 16k table is 1/2 the number of blocks of the 8k table, it would halve it.  if the data was totally scrambled, it would do basically nothing.

Really, the only way to "affect" the clustering factor in a meaningful way is to have the data on disk "sorted" -- but ask yourself this important question first:  how many ways can a tables data be sorted?  

If maintaining order (or clustering) is vital you want to read about IOT's, b*tree clusters and hash clusters -- they are all structures that make data "have a location"

ops$tkyte@ORA9IR2> drop table t1;
 
Table dropped.
 
ops$tkyte@ORA9IR2> drop table t2;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t1 as select * from all_objects order by object_id;
 
Table created.
 
ops$tkyte@ORA9IR2> create index t1_idx on t1(object_id);
 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_index_stats( user, 'T1_IDX' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> create table t2 tablespace ts_16k as select * from t1 order by object_id;
 
Table created.
 
ops$tkyte@ORA9IR2> create index t2_idx on t2(object_id);
 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_index_stats( user, 'T2_IDX' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select index_name, clustering_factor from user_indexes where index_name like 'T__IDX';
 
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
T1_IDX                                       420
T2_IDX                                       207
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t1;
 
Table dropped.
 
ops$tkyte@ORA9IR2> drop table t2;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t1 as select * from all_objects order by reverse(object_id);
 
Table created.
 
ops$tkyte@ORA9IR2> create index t1_idx on t1(object_id);
 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_index_stats( user, 'T1_IDX' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> create table t2 tablespace ts_16k as select * from t1 order by reverse(object_id);
 
Table created.
 
ops$tkyte@ORA9IR2> create index t2_idx on t2(object_id);
 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_index_stats( user, 'T2_IDX' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select index_name, clustering_factor from user_indexes where index_name like 'T__IDX';
 
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
T1_IDX                                     30679
T2_IDX                                     30678