12 Replies Latest reply: Jun 17, 2014 1:00 PM by ddf_dba_ifox RSS

    Index Rebuild

    newbieDBA

      Hi All,

       

      We were looking after index structures and rebuilding. But we were not able to find much meaningful data related to index rebuilding and how it performs on back-end for below scenario.

       

      Suppose there are large number of deletion in a table. Then what will happen at LEAF and BRANCH Level before and after Index Rebuild.

       

      Regards,

        • 1. Re: Index Rebuild
          SomeoneElse
          • 2. Re: Index Rebuild
            sybrand_b

            A B-tree index will NEVER EVER become unbalanced.

            You need to understand the B-tree concept and stop wasting your time on irrelevant index-rebuilding.

            What do you gain by rebuilding an index? Usually nothing.

             

            -----------

            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: Index Rebuild
              rp0428
              We were looking after index structures and rebuilding.

              Why?

               

              What PROBLEM are you trying to solve?

              But we were not able to find much meaningful data related to index rebuilding and how it performs on back-end for below scenario.

              Sorry - but I find that very hard to believe.

               

              Post examples of the search terms you used and the links that you examined that support your statement that you can't 'find much meaningful data'.

              • 4. Re: Index Rebuild
                Hemant K Chitale

                If there are truly a large number of DELETEs  *and* the same key values are not likely to be re-inserted into the table, you could use ALTER INDEX indexname COALESCE;

                 

                If the same key values will be reinserted, the "near-empty" leaf blocks will be reused by the inserts.

                 

                 

                Hemant K Chitale


                • 5. Re: Index Rebuild
                  Aman....

                  newbieDBA wrote:

                   

                  Hi All,

                   

                  We were looking after index structures and rebuilding. But we were not able to find much meaningful data related to index rebuilding and how it performs on back-end for below scenario.

                   

                  Suppose there are large number of deletion in a table. Then what will happen at LEAF and BRANCH Level before and after Index Rebuild.

                   

                  Regards,

                  But why you are considering the rebuilding of indexes anyways? Did you read the pdf that SomeoneElse has referred?

                   

                  Aman....

                  • 6. Re: Index Rebuild
                    newbieDBA

                    Hey,

                     

                    I understand your point but Oracle suggest that if your deletion is more than 20% of your insertion than it is advisable to rebuild an index for better performance.

                     

                    I would like to know your point of view for not rebuilding index as I have came across many DBA's who also suggest for the same for not rebuilding index.

                     

                    Regards,

                    • 7. Re: Index Rebuild
                      JustinCave

                      Where do you believe that Oracle has made this suggestion?  It's possible, I guess, that there are Metalink documents out there that make silly recommendations but they'd still be incorrect. 

                       

                      Have you read through Richard Foote's presentation (linked earlier)?  That pretty thoroughly debunks the idea that you need to rebuild a b-tree index outside of some very exceptional circumstances.

                       

                      Justin

                      • 8. Re: Index Rebuild
                        newbieDBA

                        Hi ,

                         

                        But say for example there large number of rows deleted from your table. So there will be empty leaf's at B-Tree Level.

                        Now if your perform select statement on that table, so at back-end oracle will go through every leaf present under that B-tree index including Empty ones.

                         

                        So won't this affect the performance??

                         

                        Regards,

                        • 9. Re: Index Rebuild
                          Hemant K Chitale

                          >so at back-end oracle will go through every leaf present under that B-tree index including Empty ones.

                          Then you don't understand how a B-Tree index is used.  Oracle does not have to scan every leaf block looking for a key.  It uses the root and branch blocks and pointers in leaf blocks to identify the target leaf blocks.

                           

                          I believe that the Oracle Concepts manual has an explanation with a diagram.

                           

                          Entire empty leaf blocks no longer belong to the index and need not be read.  Partially or nearly empty leaf blocks (for example those with only 1 or a few key values and/or rowids) can be handled with an ALTER INDEX indexname COALESCE

                           

                          There may be boundary conditions where a REBUILD is preferable.  For example, because a COALESCE generates redo while a REBUILD can be done as a NOLOGGING operation if you have a very very large proportion of leaf blocks to be coalesced you might decide on a REBUILD.  Note that if the deleted key values or near values are reinserted, the new index structure can suffer block splits which impact performance and index size.

                           

                           

                          Besides Richard Foote's blog posts and articles, also see

                          http://hemantoracledba.blogspot.com/2009/05/index-block-splits-90-10.html

                          http://hemantoracledba.blogspot.com/2009/05/index-block-splits-50-50.htm

                           

                          Hemant K Chitale

                          • 10. Re: Index Rebuild
                            rp0428
                            But say for example there large number of rows deleted from your table. So there will be empty leaf's at B-Tree Level.

                            But say for example that you answer Justin's question:

                            I understand your point but Oracle suggest that if your deletion is more than 20% of your insertion than it is advisable to rebuild an index for better performance.

                            You were ask to provide the link to where Oracle makes that suggestion.

                            • 11. Re: Index Rebuild
                              jgarry
                              • 12. Re: Index Rebuild
                                ddf_dba_ifox

                                newbieDBA wrote:

                                 

                                Hey,

                                 

                                I understand your point but Oracle suggest that if your deletion is more than 20% of your insertion than it is advisable to rebuild an index for better performance.

                                 

                                I would like to know your point of view for not rebuilding index as I have came across many DBA's who also suggest for the same for not rebuilding index.

                                 

                                Regards,

                                I suggest you look at MOS Document 122008.1 as it states that although the criteria in the original version of that document may have been valid it isn't any more.  There are also two links in that document to more recent MOS documents that contradict that original assumption.

                                 

                                David Fitzjarrell