Clustering factor on composite indexes

Learnt something new with clustering factor on composite indexes.  I was under the perception that if the leading column of a composite index is clustered with table, then  we end up with better CF (that is the clustering factor will be close the number of table blocks) regardless of the data distribution of other columns in this composite index.  (But as usual I am wrong with my understanding – It took only a couple of test case to prove that)

If the first column is “well sorted” (that would mean C1,ROWID is well sorted in the index with respect to the table), that does not imply that C1,C2,ROWID will be well sorted – C2 is sorted within C1 and would mess up the clustering factor just as easily as if C2 were first.

Consider:  C1 = constant, C2 = randomly arriving data, you cannot get “better” than constant for clustering factor – so,

rajesh@PDB1>

rajesh@PDB1> create table t

  2  as

  3  select 'N' as n,

  4     dbms_random.random id, a.*

  5  from all_objects a;

 

Table created.

 

rajesh@PDB1>

rajesh@PDB1> create index n_idx on t(n) nologging;

rajesh@PDB1> create index id_idx on t(id) nologging;

rajesh@PDB1> create index n_id_idx on t(n,id) nologging;

rajesh@PDB1> create index id_n_idx on t(id,n) nologging;

rajesh@PDB1>

rajesh@PDB1> select index_name,t.num_rows,t.blocks,i.clustering_factor

  2  from user_indexes i,

  3    user_tables t

  4  where i.table_name = t.table_name

  5  and i.table_name = 'T'

  6  order by 1;

 

INDEX_NAME   NUM_ROWS     BLOCKS CLUSTERING_FACTOR

---------- ---------- ---------- -----------------

ID_IDX          89347       1655             89304

ID_N_IDX        89347       1655             89304

N_IDX           89347       1655              1621

N_ID_IDX        89347       1655             89304

 

4 rows selected.

 

rajesh@PDB1>

 

Clustering factor is better on N_IDX but not on the composite index N_ID_IDX (composite index having column “n” on the leading edge). So any time ID is in the index, it drives the CF way up- regardless of its position.

Even if N non-constant but just clustered.

rajesh@PDB1>

rajesh@PDB1> create table t

  2  as

  3  select chr(ascii('a')+trunc(rownum/2000)) as n,

  4     dbms_random.random as id, a.*

  5  from all_objects a;

 

Table created.

 

rajesh@PDB1>

rajesh@PDB1> create index n_idx on t(n) nologging;

rajesh@PDB1> create index id_idx on t(id) nologging;

rajesh@PDB1> create index n_id_idx on t(n,id) nologging;

rajesh@PDB1> create index id_n_idx on t(id,n) nologging;

rajesh@PDB1>

rajesh@PDB1> select index_name,t.num_rows,t.blocks,

  2             i.clustering_factor

  3  from user_indexes i,

  4    user_tables t

  5  where i.table_name = t.table_name

  6  and i.table_name = 'T'

  7  order by 1;

 

INDEX_NAME   NUM_ROWS     BLOCKS CLUSTERING_FACTOR

---------- ---------- ---------- -----------------

ID_IDX          89347       1655             89293

ID_N_IDX        89347       1655             89293

N_IDX           89347       1655              1623

N_ID_IDX        89347       1655             86951

 

4 rows selected.

 

rajesh@PDB1>

 

It would only be true if the leading column where nearly unique

 

rajesh@PDB1>

rajesh@PDB1> create table t

  2  as

  3  select rownum as n,

  4    dbms_random.random id, a.*

  5  from all_objects a;

 

Table created.

 

rajesh@PDB1>

rajesh@PDB1> create index n_idx on t(n) nologging;

rajesh@PDB1> create index id_idx on t(id) nologging;

rajesh@PDB1> create index n_id_idx on t(n,id) nologging;

rajesh@PDB1> create index id_n_idx on t(id,n) nologging;

rajesh@PDB1>

rajesh@PDB1> select index_name,t.num_rows,t.blocks,i.clustering_factor

  2  from user_indexes i,

  3    user_tables t

  4  where i.table_name = t.table_name

  5  and i.table_name = 'T'

  6  order by 1;

 

INDEX_NAME   NUM_ROWS     BLOCKS CLUSTERING_FACTOR

---------- ---------- ---------- -----------------

ID_IDX          89347       1693             89288

ID_N_IDX        89347       1693             89288

N_IDX           89347       1693              1657

N_ID_IDX        89347       1693              1657

 

4 rows selected.

 

rajesh@PDB1>