This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Mar 13, 2008 5:53 AM by Jonathan Lewis Go to original post RSS
  • 15. Re: Leaf nodes and Blevel in an index
    108476 Journeyer
    Currently Being Moderated
    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
    311441 Employee ACE
    Currently Being Moderated
    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
    311441 Employee ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    311441 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Can someone kindly sumarise the answer .Please
  • 23. Re: Leaf nodes and Blevel in an index
    mbobak Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Many thanks all for the replies and help
  • 26. Re: Leaf nodes and Blevel in an index
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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