Re: Don Burleson: The Index Rebuild Debate

> Here we are ... A MAGIC EXAMPLE THAT REDUCES THE CLUSTERING FACTOR WHEN
> YOU REBUILD THE INDEX ! ! !
>
> As we've always said, its important to be able to backup claims with
> simple examples. So here we go...
>
> 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! An index rebuild has altered the clustering factor
>
> (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.
>
>
> ha ha ha ha...sorry, couldn't resist
>
>
> --
> Connor McDonald
> Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> ISBN: 1590592174
>
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: connor_mcdonald_at_yahoo.com
>
> Coming Soon! "Oracle Insight - Tales of the OakTable"
>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
> and...he will sit in a boat and drink beer all day"
>
> ------------------------------------------------------------

You couldv'e just typed in any numbers you wanted. Like this:

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

CLUSTERING_FACTOR

              2728 Received on Tue Jun 08 2004 - 12:01:29 CDT