This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Mar 13, 2008 5:53 AM by Jonathan Lewis RSS

Leaf nodes and Blevel in an index

bsac14 Newbie
Currently Being Moderated
Hi,

Can someone please clarify my doubt

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

Please help
  • 1. Re: Leaf nodes and Blevel in an index
    damorgan Oracle ACE Director
    Currently Being Moderated
    1. No.

    2. No.

    Where are you getting this stuff? Certainly not from the Oracle docs or any reputable source such as Jonathan Lewis or Richard Foote.
  • 2. Re: Leaf nodes and Blevel in an index
    471322 Newbie
    Currently Being Moderated
    Oh no! Like you can't see what's coming.


    Tom
  • 3. Re: Leaf nodes and Blevel in an index
    523455 Newbie
    Currently Being Moderated
    Dmorgan is true.

    We should opt for more flattened indexes.
  • 4. Re: Leaf nodes and Blevel in an index
    stevencallan Expert
    Currently Being Moderated
    What are you talking about?
  • 5. Re: Leaf nodes and Blevel in an index
    523455 Newbie
    Currently Being Moderated
    If one observe that the BLEVEL is getting deep than reduce it to make it flatten.
  • 6. Re: Leaf nodes and Blevel in an index
    stevencallan Expert
    Currently Being Moderated
    No.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730
  • 7. Re: Leaf nodes and Blevel in an index
    523455 Newbie
    Currently Being Moderated
    Yes you are true ,

    But upto 9i we use to do it.

    Please refer Metalink doc 77574.1 for reference.
  • 8. Re: Leaf nodes and Blevel in an index
    stevencallan Expert
    Currently Being Moderated
    Well, we used to use rollback segments, but we don't do that anymore either.
  • 9. Re: Leaf nodes and Blevel in an index
    108476 Journeyer
    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?

    *******************************************************

    Subject: Guidelines on When to Rebuild a B-Tree Index
    Last Revision Date: 23-APR-2007

    "You have to periodically check your indexes to see if they become skewed and, therefore, good candidates for rebuild.

    A skewed index has many records clumped close together on the index tree due to their similar indexed values. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance. It is important to periodically examine
    your indexes to determine if they have become skewed and might need to be rebuilt."

    "The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL.

    Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. As database are getting bigger and bigger, BLEVEL may not be a good indicator of time to rebuild the index. BLEVEL > 4 may suggest an evaluation of whether the partitioning option could help you."
  • 10. Re: Leaf nodes and Blevel in an index
    108476 Journeyer
    Currently Being Moderated
    Hi Steve,
    No.
    Sorry, it's not quite that simple.

    There is lots of reproduceable evidence that "flatter indexes" perform faster.

    Please read:

    http://www.dba-oracle.com/art_so_blocksize.htm
  • 11. Re: Leaf nodes and Blevel in an index
    stevencallan Expert
    Currently Being Moderated
    Let me re-phrase the "no" this way: what control do we, as mere mortals in the presence of the great and mysterious Oz-acle, have over the depth? In other words, when the index build/creation is all said and done, what control do we have over what Oracle decides to use for depth?
  • 12. Re: Leaf nodes and Blevel in an index
    108476 Journeyer
    Currently Being Moderated
    Hi Steve,

    Before we start WWIII, let's make it clear that I DO NOT advocate rebuilding unless it is necessary. I charge by the hour; and I will not waste my client's money doing something that is not necessary.

    Also, note that Index rebuilding has been automated within OEM, it's right here in Oracle Magazine:

    http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html

    Remember, there are TWO reasons for index rebuilding:

    - Saving Space -
    - Improving the performance of multi-block reads (index range scans, index FFS)


    ********************************************************************
    what control do we,as mere mortals in the presence of the great and mysterious Oz-acle, have over the depth?
    - Rebuild the index, coalesce, shrink space, that's it. At the data block level, Oracle does not re-balance the index in real-time, it would be too time-consuming.

    ********************************************************************
    what control do we have over what Oracle decides to use for depth?
    Well, the index blocksize makes a big difference in the number of levels, and that's one reason that some shops implement a 32k blocksize, just for indexes that experience multi-block reads:

    http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm

    Steve, some people make sweeping over-generalizations (it's RARE or UNCOMMON), but that's all doo-doo. Rebuilding depends SOLELY on the volatility of your index.

    Don't trust anyone who makes statements like "It's never needed", or "you rarely rebuild indexes". Like evrything in Oracle, "it depends".

    For example, in high DML systems like Clintrial, you have to rebuild Oracle index nightly to maintain any decent performance! Other applications NEVER need index rebuilding . . . .

    It depends . . . . .

    When I charge my clients to rebuild indexes, I don't guess. I measure, test and show them proof that the rebuild reduced I/O.

    You want to see too-many-levels in an index simply insert a zillion rows in one part of the index, then delete 20% of the index in another sequence, and repeat 100 times . . . . .

    ********************************************************
    Kim Floss said it well:

    http://searchoracle.techtarget.com/generic/0,295582,sid41_gci1050448,00.html

    There are many myths and legends surrounding the use of Oracle indexes, especially the ongoing debate about rebuilding of indexes for improving performance. Some experts claim that periodic rebuilding of Oracle b-tree indexes greatly improves space usage and access speed, while other experts maintain that Oracle indexes should "rarely" be rebuilt. Interestingly, Oracle reports that the new Oracle10g Automatic Maintenance Tasks (AMT) will automatically detect indexes that are in need of re-building. Here are the pros and cons of the issue:

    Arguments for Index Rebuilding. Many Oracle shops schedule periodic index rebuilding, and report measurable speed improvements after they rebuild their Oracle b-tree indexes.

    Arguments against Index Rebuilding. Some Oracle in-house experts maintain that Oracle indexes are super-efficient at space re-use and access speed and that a b-tree index rarely needs rebuilding. They claim that a reduction in Logical I/O should be measurable, and if there were any benefit to index rebuilding, someone would have come up with "provable" rules.

    She goes on to note some index rebuilding criteria:

    "The short answer is no, there is no 100% complete, definitive list. But, here are some things to start with:

    - Index levels > 3

    - Pct_used < 75%

    - More than 20% of the rows have been deleted (space is not automatically reused)

    - Index is becoming unclustered, and performance is degrading (causing increases in number of blocks to be read) – while unloading, resorting, and reloading the data in a table may provide better performance, this is an additional maintenance activity that needs to be performed, and can be difficult to keep the rows in their proper sequence, if the table gets a lot of insert/update/delete activity. "
  • 13. Re: Leaf nodes and Blevel in an index
    311441 Employee ACE
    Currently Being Moderated
    So, who do we believe, Morgan's "respected" experts
    or MetaLink?
    Hi Don

    You believe what's correct ;)

    Note the metalink note doesn't really check for skewness at all, but makes some kinda generic assumption that only an index that has a blevel of 0-3 is OK and everything else is "High".

    However, it then makes the disclaimer that we never really had big indexes prior to 9i and so a blevel of 4 can't really be OK, right ?

    Ummm, but what if you just happened to have a big index prior to 9i, what then ?

    And now that most shops are on at least 9i or above, it makes the whole note redundant as it also says that now indexes are bigger, a blevel of 4 and above is actually reasonable and so the blevel "may not be a good indicator of time to rebuild the index" after all and you should look at partitioning the thing !!

    So what the note is basically saying, don't have big indexes in a database prior to 9i else we may think to rebuild it (even if it doesn't need it as it's just big index after all) and if you're on 9i or above don't rebuild the index, partition it, as it's possibly just a big index anyways.

    Ummm, really useful note that ...

    One last point.

    If an index really did have some parts "accessed more frequently than others", which many index are likely to have, how precisely does rebuilding the index help in this regard ?

    Answer, not a lot as the rebuild leaves the index in the same logical order and the same parts of the index are going to be accessed just as frequently as previously.

    Rebuilding an index just because it has parts that are accessed more frequently than other parts of an index sounds like the begining of yet another index related myth to me ...

    Don, I would strongly recommend that you not assume metalink is error free ...

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 14. Re: Leaf nodes and Blevel in an index
    108476 Journeyer
    Currently Being Moderated
    Hi Richard,

    HeHe, were your ears burning? I've been expecting you!
    Rebuilding an index just because it has parts that are accessed more frequently than other parts of an index sounds like the begining of yet another index related myth to me ...
    Hey, I never said that!!!

    Don't shoot the messenger. . . . . Shoot MetaLink!
    Don, I would strongly recommend that you not assume metalink is error free ...
    Gee, ya think so? ; )

    I was being facetious . . . .

    Didn't we do this same argument last month?

    Maybe we should schedule this discussion on a cron.
1 2 Previous Next