9 Replies Latest reply: Nov 22, 2010 3:51 AM by 311441 RSS

    Rebuild index vs Analyze index

    794905
      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
          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
            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
              Pavan Kumar
              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
                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
                  Pavan Kumar
                  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
                    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
                      Pavan Kumar
                      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
                        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
                          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/