Clustering factor


On comp.databases.oracle.server, someone claimed that rebuilding an index might alter the clustering factor. This is patently untrue, but the following example shows that you can prove anything with Oracle if you're deceitful enough :-)

SQL> create table T ( x number );

Table created.

SQL> insert into T
  2  select dbms_random.value
  3  from all_objects
  4  where rownum < 10000;

9999 rows created.

SQL> create index TX on T ( x ) compute statistics;

Index created.

SQL> select clustering_factor
  2  from user_indexes
  3  where index_name = 'TX';

CLUSTERING_FACTOR
-----------------
             9737

SQL> alter index TX rebuild compute statistics;

Index altered.

SQL> select clustering_factor
  2  from user_indexes
  3  where index_name = 'TX';

CLUSTERING_FACTOR
-----------------
             4868

Voila!
(Now I suppose I should also include the little bit of extra code that was used to "assist" with this example)

SQL> create or replace trigger tweak_clustfac
  2  after alter on schema
  3  declare
  4    pragma autonomous_transaction;
  5    j number;
  6    obj varchar2(100);
  7  begin
  8    select ora_dict_obj_name into obj from dual
  9    where ora_dict_obj_name = 'TX';
 10    dbms_job.submit(j,
 11      'begin
 12        update ind$
 13        set clufac = trunc(clufac / 2)
 14        where obj# =
 15           ( select obj# from obj$
 16             where name = ''TX'' );
 17        commit;
 18       end;');
 19    commit;
 20  exception
 21    when no_data_found then null;
 22  end;
 23  /

Trigger created.

So here we trap 'alter' commands for the schema, check to see if it was the index and then submit a job to update the clustering factor directly. This is very very very bad.... :-)