This discussion is archived
3 Replies Latest reply: Mar 30, 2013 10:31 AM by Jonathan Lewis RSS

Index Rebuilding troble

Santosh Pradhan Explorer
Currently Being Moderated
Hi ,
we have table gen_1
size 80gb
fragmentation 12gb


We observed that the blevel of index on this table has reached to 4 and when we rebuild an index it goes to blevel 3 .

But when i gather statts on this table ,the blevel for that same index goes to 4 automatically .

3-4 time i observed this ...

Can anyone tell me , whyyyy??
  • 1. Re: Index Rebuilding troble
    sb92075 Guru
    Currently Being Moderated
    Santosh Pradhan wrote:
    Hi ,
    we have table gen_1
    size 80gb
    fragmentation 12gb


    We observed that the blevel of index on this table has reached to 4 and when we rebuild an index it goes to blevel 3 .

    But when i gather statts on this table ,the blevel for that same index goes to 4 automatically .

    3-4 time i observed this ...

    Can anyone tell me , whyyyy??
    It is unsubstantiated Urban Legend that say Index should be rebuilt when BLEVEL=4.
    Some indexes are normally that deep & there is NOTHING that can be done to change it.
    I would expect that the indexes where you observe this behavior are "wide"; have multiple columns as part of the composite Index.
  • 2. Re: Index Rebuilding troble
    damorgan Oracle ACE Director
    Currently Being Moderated
    SB is correct ... you aren't having trouble rebuilding an index ... the trouble is, most likely, that you are trying to rebuild the index. Rebuilding indexes is almost always the wrong thing to do and in your case likely a total waste of time.

    In those circumstances where an index has issues COALESCE is generally a far better solution than rebuild.

    Unless you can point to a specific metric ... post the SQL and the result ... I'd recommend finding something else to do with your time.
  • 3. Re: Index Rebuilding troble
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    sb92075 wrote:

    It is unsubstantiated Urban Legend that say Index should be rebuilt when BLEVEL=4.
    That is true - but in this case the OP says that when he rebuilds the index the level drops from 4 to 3, and that is almost a sufficient argument to do the rebuild if his observation is correct. However he could do with a better measure of the efficiency of his index and how much (benefit if any) the rebuild introduces.

    Having said that I think we need to know how he decides that the blevel of the index is 3 after the rebuild if he hasn't collected stats on it - after all, he says that when he collects stats the blevel changes to 4. Since collecting stats shouldn't (I'd like to say can't possibly) change the blevel of the index I suspect that this claim that the blevel drops to 3 on the rebuild is flawed.

    Regards
    Jonathan Lewis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points