Clustering_Factor = 0

I looked at clustering_factor in the previous post and wondered what might cause it to be well below the number of blocks in the table. Chris Saxon suggested it might be due to rows with null values. I decided to look first at the two indexes with a clustering_factor of zero in the same Oracle 11.2 database and checked which columns they were on. Then I looked to see how many of the rows had null values in the columns concerned. The table had > 100 million rows and I found it much quicker to see if any rows had not null values instead. In both cases, there were none at all i.e. all rows in the table had null values in the columns in question:

 

SQL> select owner, index_name

  2  from dba_indexes

  3  where table_owner = 'UIMSMGR'

  4  and table_name = 'UABOPEN'

  5  and clustering_factor = 0

  6  /

 

OWNER                     INDEX_NAME

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

TFE_INDEXES               TFE_UABOPEN_COPA_CODE

UIMSMGR                   UABOPEN_COURT_CASE_INDEX

 

SQL> select column_position, column_name

  2  from dba_ind_columns

  3  where index_owner = 'TFE_INDEXES'

  4  and index_name = 'TFE_UABOPEN_COPA_CODE'

  5  order by 1

  6  /

 

COLUMN_POSITION COLUMN_NAME

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

              1 UABOPEN_COPA_CODE

 

SQL> select column_position, column_name

  2  from dba_ind_columns

  3  where index_owner = 'UIMSMGR'

  4  and index_name = 'UABOPEN_COURT_CASE_INDEX'

  5  order by 1

  6  /

 

COLUMN_POSITION COLUMN_NAME

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

              1 UABOPEN_COURT_CASE_NUMBER

 

SQL> select count(*)

  2  from uabopen

  3  where uabopen_copa_code is not null

  4  /

 

  COUNT(*)

----------

         0

 

SQL> select count(*)

  2  from uabopen

  3  where uabopen_court_case_number is not null

  4  /

 

  COUNT(*)

----------

         0

 

SQL>

 

As for the indexes with clustering_factor > 0 but less than the number of blocks in the table, I guess that some but not all of the rows had null values in the indexed columns. This is something I will leave you to check for yourselves if you feel so inclined.