This discussion is archived
9 Replies Latest reply: Nov 22, 2010 1:51 AM by 311441 RSS

Rebuild index vs Analyze index

794905 Newbie
Currently Being Moderated
Hi All,

I am realy confused about rebuilding index versus Analyzing index.

Could anyone plz help me out what is the diffrence between them.

How to Perform analyze of indexes and Rebuld of Indexes for both Oracle 9i and 10g databases.


Thanks a lot
  • 1. Re: Rebuild index vs Analyze index
    ora_tech Expert
    Currently Being Moderated
    Hi,

    I am realy confused about rebuilding index versus Analyzing index.
    Could anyone plz help me out what is the diffrence between them.
    How to Perform analyze of indexes and Rebuld of Indexes for both Oracle 9i and 10g databases.
    Refer:

    Difference between Index Rebuild and Analyze indexes.
    http://asktom.oracle.com/pls/asktom/f?p=100:11:5309168164028575::::P11_QUESTION_ID:6601312252730
    Re: Several questions about analyzing table to rebuild the index

    Thanks,
    X A H E E R
  • 2. Re: Rebuild index vs Analyze index
    Lubiez Jean-Valentin Guru
    Currently Being Moderated
    Hello,

    I am realy confused about rebuilding index versus Analyzing index.
    These operations are completely different.

    When you ANALYZE an Index you gather Statistics for the Optimizer on this Index. By that way, the Optimizer will make a more accurate estimation of the COST and could choose a better execution Plan.

    When you REBUILD an index you recreate its Segment its leaf and branch Blocks. Generally you do a REBUILD when you intend to move an Index from one Tablespace to another one, when an Index became unusable or invalid. Or when the Index became so much fragmented (after a lot of insert / update / delete) that you may reorganize it to improve performance.

    After Rebuilding Indexes generally its better to Analyze them so as to get reliable statistics.

    Please, find enclosed a link about this topic:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730
    How to Perform analyze of indexes and Rebuld of Indexes for both Oracle 9i and 10g databases
    To ANALYZE an Index you should use the Package DBMS_STATS .

    To REBUILD an Index you have (from Oracle 7) the following statements:
    ALTER INDEX <Index_name> REBUILD ... ;
    Hope this help.
    Best regards,
    Jean-Valentin
  • 3. Re: Rebuild index vs Analyze index
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Refer to : http://www.dba-oracle.com/art_index1.htm
    Further oracle documentation.

    - Pavan Kumar N
  • 4. Re: Rebuild index vs Analyze index
    sybrand_b Guru
    Currently Being Moderated
    If you want to promote Donald Burleson's view on this at least also refer to the paper of Richard Foote so people can decide from themself.

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 5. Re: Rebuild index vs Analyze index
    PavanKumar Guru
    Currently Being Moderated
    Hi Sybrand,

    I will not regularly post across (or) would not asked people to refer to links, I like one or two of his links. Yes, I agree with you, I much prefer to "Richard foote" index internals and it's very much knowledgable. I could not get the link, in right time.

    Op,'
    try to refer to the below link of Richard foote - very much insight of index internals
    http://richardfoote.wordpress.com/category/index-rebuild/

    - Pavan Kumar N
  • 6. Re: Rebuild index vs Analyze index
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Pavan Kumar wrote:
    Hi,

    Refer to : http://www.dba-oracle.com/art_index1.htm
    Further oracle documentation.
    I'm sure there are one or two Burleson articles which are worth reading - but this is not one of them. Note - for example - this pair of quotes:

    <ul>
    +"Hence, an Oracle index may have four levels, but only in those areas of the index tree where the massive inserts have occurred."+

    +"Note that Oracle indexes will “spawn” to a fourth level only in areas of the index where a massive insert has occurred, such that 99% of the index has three levels, but the index is reported as having four levels. "+

    </ul>

    This is wrong. All leaf blocks in a b-tree index are at the same level - you don't find parts of it a different levels.


    Note also this advice on using the results of "analyze index validate structure":

    <ul>
    "We might want to rebuild an index if the “block gets” per access is greater than five, since excessive “blocks gets” indicate a fragmented b-tree structure"
    </ul>

    This is not a good reason for rebuilding an index. Oracle calculates the column "BLKS_GETS_PER_ACCESS" as roughly "blevel + 1 + (rows per index key value)/ 2" - so any index with more than 10 rows per key will have blks_gets_per_access greater than 5.


    I suggest that you remove that article from your list of suggested reading until it has been reviewed and corrected.


    Regards
    Jonathan Lewis
  • 7. Re: Rebuild index vs Analyze index
    PavanKumar Guru
    Currently Being Moderated
    Hi Jonathan,

    Thanks for providing your valuable comments on the article inspite of your busy schedule.
    As you stated I will remove the article from my list.. Sir.. !!

    Very much thankful Jonathan.. !!

    - Pavan Kumar N
  • 8. Re: Rebuild index vs Analyze index
    CKPT Guru
    Currently Being Moderated
    You can see the posts of experts by jonathan
    I am realy confused about rebuilding index versus Analyzing index.
    tell us you are getting confused why we need to ananlyze before reubild index? if so

    if index analyzed the whole statistics of index will be gathered.... then you can check what is the hieght of the index.. according to the height of the index you need to take step is index need to be really rebuild or not...

    lets see furhter posts from experts if not clear..Thanks
  • 9. Re: Rebuild index vs Analyze index
    311441 Employee ACE
    Currently Being Moderated
    CKPT wrote:
    You can see the posts of experts by jonathan
    I am realy confused about rebuilding index versus Analyzing index.
    tell us you are getting confused why we need to ananlyze before reubild index? if so

    if index analyzed the whole statistics of index will be gathered.... then you can check what is the hieght of the index.. according to the height of the index you need to take step is index need to be really rebuild or not...

    lets see furhter posts from experts if not clear..Thanks
    OK, so you determine the height of an index is (say) 4. What then ? If you decide to rebuild the index and the index remains at a height of 4, what now ? Was it really worth doing and do you rebuild it again as the index height is still 4 and still within your index rebuild criteria ? At what point do you decide that rebuilding the index just because it has a height of 4 is a total waste of time in this case ?

    OK, so you determine the index only has a height of (say) 3, does that mean you don't rebuild the index ? But what if by rebuilding the index, the index now reduces to a height of just 1 ? Perhaps not rebuilding the index even though it has just a height of 3 and doesn't currently meet your index rebuild criteria is totally the wrong thing to do and a rebuild would result in a significantly leaner and more efficient index structure ?

    So what if it's pointless rebuilding an index with a height of 4 but another index with a height of 3 is a perfect candidate to be rebuilt ?

    Perhaps knowing just the height of an index leaves one totally clueless after all as to whether the index might benefit from an index rebuild ...

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/

Legend

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