1 2 Previous Next 26 Replies Latest reply: Mar 13, 2008 7:53 AM by Jonathan Lewis Go to original post RSS
      • 15. Re: Leaf nodes and Blevel in an index
        108476
        Hi Richard,

        You have written volumes about this (Wow, 67 blog entries!), and I think that you did agree that index rebuilding can help performance in certain cases:

        http://www.google.com/search?&q=site%3Arichardfoote.wordpress.com+index+rebuild%7Crebuilding

        Could you please summarize these cases for us, and put this to bed?

        How about it, Richard, can you summarize your research, in a nutshell?
        • 16. Re: Leaf nodes and Blevel in an index
          Richard Foote
          Hi Don

          Couple of points.

          "Improving the performance of multi-block reads (index range scans, index FFS)". When does Oracle perform multi-block reads when performing an index range scan ?

          How does the index blocksize make a "big difference to the number of levels" ?

          It will generally change the level of an index by either 0 or in some cases 1 level. Define "big" ...

          The Kim Floss reference. Aren't they your words that "she said well": http://www.dbazine.com/oracle/or-articles/burleson18

          Cheers ;)

          Richard Foote
          http://richardfoote.wordpress.com/
          • 17. Re: Leaf nodes and Blevel in an index
            Richard Foote
            Hi Don

            Summarising 67 blog entries in a nutshell would require a really big nutshell :)

            Absolutely rebuilding an index can help performance in certain cases, I have a "classic" example in my index rebuilding presentation:

            (http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf)

            on pages 132-135.

            But I'm a humble member of the "Rebuild An Index Rarely" club ;)

            I discuss these conditions in some detail in my seminars but if you want a little nutshell, see page 145 of the above presentation. Those 3 points summarise it well ...

            Cheers

            Richard Foote
            http://richardfoote.wordpress.com/
            • 18. Re: Leaf nodes and Blevel in an index
              108476
              Hi Richard,
              How does the index blocksize make a "big difference to the number of levels" ?
              Sorry, did I say that? I must have had a senior moment . . . .

              I've been a differences, yes, but I don't remember saying "big" (If I did, paste a link and I'll gladly change it!)

              However, on a multi-gig index, it would be easy to show the blevel difference between a 2k and 32k blocksize. I'll bet that you could show it!
              • 19. Re: Leaf nodes and Blevel in an index
                108476
                Hi Richard,
                Summarising 67 blog entries in a nutshell would require a really big nutshell :)
                Ah yes, but it's a valuable skill to have . . . .
                But I'm a humble member of the "Rebuild An Index Rarely" club ;)
                That's fine . . . . . I agree that overall it's rare, I just don't care for sweeping generalizations . . . .
                see page 145 of the above presentation.
                PDF's suck, causes memory leaks on my crapware Windows.

                Can't you just paste it in for us?
                • 20. Re: Leaf nodes and Blevel in an index
                  Richard Foote
                  Hi Richard,
                  How does the index blocksize make a "big
                  difference to the number of levels" ?

                  Sorry, did I say that? I must have had a senior
                  moment . . . .

                  I've been a differences, yes, but I don't remember
                  saying "big" (If I did, paste a link and I'll gladly
                  change it!)
                  Hi Don

                  In your post to Steve in this thread that I replied to.

                  Cheers

                  Richard Foote
                  http://richardfoote.wordpress.com/
                  • 21. Re: Leaf nodes and Blevel in an index
                    bsac14
                    Dear all,

                    Thanks for your all the replies. Can someone summarise and clarify my doubts :


                    1. If there are many leaf nodes does it mean the index has undergone a lot of updates.

                    2.if the blevel is greater than 4 ,does it mean the index has undergone a lot of updates and it requires rebuilding
                    • 22. Re: Leaf nodes and Blevel in an index
                      bsac14
                      Can someone kindly sumarise the answer .Please
                      • 23. Re: Leaf nodes and Blevel in an index
                        mbobak
                        1.) If there are many leaf nodes, does it mean the index has undergone a lot of updates?

                        Well, yes, I suppose it has. Oracle will automatically grow the index structure, by adding leaf blocks, as necessary, to support the volume of data in the index. An index should not have a huge number of leaf blocks, unless there is now, or was in the past, a lot of data in the index, causing it to grow in size.

                        2.) If the blevel is greater than 4, does it mean the index has undergone a lot of updates and it requires rebuilding.

                        If the index's blevel is greater than 4, then it's probably got quite a lot of data in it. This, in and of itself, is neither good nor bad, nor does it say anything about the quality or condition of the index. The blevel is a by-product of the size of the index. The more data you stuff into an index, the more the index will grow. Eventually, the index will grow to such a state that the root block will split. When that happens, the index's blevel increases. If the index contains enough data, eventually that will happen enough times that the blevel will grow larger than 4. The only thing that tells you, is that the index has a lot of data in it.

                        For more information about indexes, how they grow, and if/when they need to be rebuilt, see Richard Foote's most excellent paper, "Oracle B-Tree Index Internals: Rebuilding the Truth". Do a google search for "rebuilding the truth richard foote", and you should find the paper easily.

                        -Mark
                        • 24. Re: Leaf nodes and Blevel in an index
                          Jonathan Lewis
                          The original reply from DAMorgan is correct:

                          1: No
                          2: No

                          An index may have many leaf blocks and blevels simply because it covers a large amount of data and has fairly long key values.

                          But a blevel of 4 is suspect if you have short keys or a relatively small table. (Things can go badly wrong with bitmap indexes, though, prior to 10.2 - if you do even a fairly small number of single row updates).

                          If you want some idea of how to think through the arithmetic, there's an item on my blog that runs through a few numbers (showing an index with blevel 3 covering 1.6 Billion rows when the blocks are 8Kb and the key values are only 16 bytes long - which means about 27 bytes per key including overheads) - http://jonathanlewis.wordpress.com/2007/03/18/thinking-big/

                          There are also a couple of articles on my website about recognising whether or not your index is not using space efficiently:

                          http://www.jlcomp.demon.co.uk/index_efficiency.html
                          http://www.jlcomp.demon.co.uk/index_efficiency_2.html

                          Regards
                          Jonathan Lewis
                          http://jonathanlewis.wordpress.com
                          http://www.jlcomp.demon.co.uk

                          Message was edited by: Jonathan Lewis
                          Added suggestion for reference material.
                          • 25. Re: Leaf nodes and Blevel in an index
                            bsac14
                            Many thanks all for the replies and help
                            • 26. Re: Leaf nodes and Blevel in an index
                              Jonathan Lewis
                              Please refer Metalink doc 77574.1 for reference.
                              Great note, thanks!

                              So, who do we believe, Morgan's "respected" experts or MetaLink?
                              I have to disagree with your evaluation. It was such a bad note that I filled in the Metalink feedback form pointing out some of the defects and suggesting it be taken off Metalink until it had been rewritten.

                              I also suggested that note 122008.1 "Script to list all indexes that benefit from a rebuild" which referenced 77574.1 should also be withdrawn for propagating the same advice.

                              At present both notes seem to have disappeared from Metalink. It will be interesting to see how they have changed when (or if) they re-appear.

                              Regards
                              Jonathan Lewis
                              http://jonathanlewis.wordpress.com
                              http://www.jlcomp.demon.co.uk
                              1 2 Previous Next