Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Don Burleson: The Index Rebuild Debate

Re: Don Burleson: The Index Rebuild Debate

From: Richard Foote <richard.foote_at_tbigpond.nospam.com>
Date: Wed, 09 Jun 2004 13:36:53 GMT
Message-ID: <VnExc.2124$uB3.1991@news-server.bigpond.net.au>


"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:40C5CCB8.7F19_at_yahoo.com...
> 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
>

Hi Connor,

LOL !! You warned me about this previously ;)

Like I said before, if only we can channel your powers towards good rather than evil !!

Cheers ;)

Richard Received on Wed Jun 09 2004 - 08:36:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US