Re: Don Burleson: The Index Rebuild Debate

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 08 Jun 2004 22:27:04 +0800
Message-ID: <40C5CCB8.7F19@yahoo.com>

Richard Foote wrote:
>
> OK Don, technical issue number 1.
>
> In my Index Rebuild presentation you seem to have taken exception to
> (www.actoug.org.au/Downloads/oracle_index_internals.pdf) , I mention a
> couple of quotes of yours.
>
> First quote:
>
> "Note that Oracle indexes will spawn to a fourth level only in areas of the
> index where a massive insert has occurred, such that 99% of the index has
> three levels, but the index is reported as having four levels."
>
> This comes from an article that you Don, yes you, wrote and promoted in this
> very newsgroup in the "Index Rebuilding" thread in January last year
> (http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&th=6bf6a5904b3a2189&seek
> m=o%25WZ9.61302%24c41.1415158%40news2.telusplanet.net#link1)
>
> I suggest in my presentation that this is utter rubbish and a silly Oracle
> myth. So please Don, explain why you are correct and I'm so wrong. Don,
> explain how an Oracle B*tree index can become unbalanced in the manner you
> describe. Supply us with evidence, a test case, just one example of where
> this is possible. It's your claim Don, come on, prove it. If you wish, you
> can even make reference to the quote that Niall mentions in his excellent
> post where you make exactly the same claim.
>
> While doing so, you may also want to explain why all your posts in that
> thread and most of your others have subsequently disappeared from the
> archives? It's all very odd isn't it ? Why have they been removed Don, you'
> re not trying to hide something are you ? I've asked this of you in the past
> with no response, perhaps you might want to take this opportunity to explain
> yourself now ?
>
> Second quote:
>
> "If the index clustering is high, an index rebuild may be beneficial"
>
> This comes from your "infamous" Inside Oracle Indexes article
> (http://www.dbazine.com/burleson18.shtml).
>
> I also suggest in my presentation that this is utter rubbish and another
> Oracle myth. So again, please Don, explain why you're correct and why I'm so
> wrong. Don, explain why on earth if you have a high CF, you would consider
> an index rebuild. Supply us with evidence, a test case, just one example of
> how the CF changes after a rebuild. I strongly suggest to you Don that near
> the end of your presentation (point 2) where this quote originates that your
> entire discussion is absolute rubbish. Completely wrong. Because Don, if you
> delete "all people whose last_name begins with the letter K", the result on
> the index would be *exactly the same regardless of the CF*. Prove me wrong
> Don, please do. And if the CF doesn't change Don, and the rebuild criteria
> remains the same afterwards, please Don what was the purpose of the rebuild
> if the index simply needs again to be immediately rebuilt ? Don, please show
> your Oracle Guruness and explain yourself. And please, play fair and debate
> the issue *before* you go and change the article again.
>
> The floor's all yours Don, debate away .
>
> Or would you rather nibble on some cheese ? Squeak Squeak.
>
> Richard

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"

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

Received on Tue Jun 08 2004 - 09:27:04 CDT