I was looking at a post on the MOSC forums today about the Clustering Factor (CF) for an index. One thing that people tend to forget when talking about the CF is that while the DBA can do some reorg activity to improve the CF for an index, it will potentially come at the expense another index for that same table. Consider this example which I provided in that thread.
Here I have a table with two indexes. Its the only table in my schema. One index (IDX2) has a CF much higher than the other (IDX1).
SQL> select index_name,clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR --------------- ----------------- MY_TAB_IDX2 135744 MY_TAB_IDX1 2257
The DBA want’s to “fix” this issue. The DBA wants to reduce the CF for IDX2. The best way to do that is to pull the data out of the table and then insert back, sorted by the column(s) IDX2 is built on.
SQL> create table my_tab_temp as select * from my_tab;
Table created.
SQL> truncate table my_tab;
Table truncated.
Are you an extremely busy person and spend almost 24 hours at your office? If such is the case then you also need to consider that impotency can be an early sign of heart disease. purchase generic cialis You have to register your names sildenafil generic cheap link to the site with all relevant information regarding you and related to the communication with phone number. In this case it seems that the attacked website was handcoded with PHP rather than it relying on an CMS script. levitra 20mg generika This is viagra samples from doctor when generic medicines came into the picture.
SQL> insert into my_tab select * from my_tab_temp order by pk_id;
135795 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'MY_TAB',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select index_name,clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR --------------- ----------------- MY_TAB_IDX2 2537 MY_TAB_IDX1 135747
Now the CF for IDX2 has definitely improved. But look at the CF on IDX1. It got much worse. In fact, the two indexes seemed to have flipped the CF values. If I attempt antoher reorg, this time ordering by the IDX1 column(s), then the CF values will flip again.
The moral of this story is that one can’t guarantee that improving the CF for one index won’t have a negative affect on another index of that table.
