3 Replies Latest reply: Mar 30, 2013 12:31 PM by Jonathan Lewis RSS

    Index Rebuilding troble

    Santosh Pradhan
      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
          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
            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
              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