Index Rebuild
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Index Rebuild

  1. #1
    Join Date
    May 2009
    Posts
    32

    Index Rebuild

    Hi Friends,

    I have quick question. When and why should I rebuild my indexes. Thanks in advance for your help.

  2. #2
    Join Date
    Apr 2006
    Posts
    377

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,975
    Quote Originally Posted by ebrian View Post
    Amazing, it takes 166 pages to say that based on Oracle using a self balancing B-Tree index, indexes should not need to be rebuilt. Here is something that explains the storage structure for a btree index. http://www.virtualmachinery.com/btreeguide.htm or
    http://www.bluerwhite.org/btree/

    Back when Oracle 7.3.4 was the standard, and if a database was using 4k or 2k block sizes, it was possible for indexes to reach the maximum number of extents. Under that situation you would need to rebuild an index to specify different storage parameters. However, with the advent of Oracle 8i, and locally managed tables, this is not longer a concern. So IMHO, if there was ever a valid reason to build indexes, it was probably more about managing extents than anything else.
    this space intentionally left blank

  4. #4
    Join Date
    Mar 2001
    Location
    India
    Posts
    58
    Hi,

    Only hot indexes or volatile indexes are needed to be rebuild.Otherwise not needed to rebuild regular.

    Thanks and regards,
    Gitesh
    http://www.dbametrix.com

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    Quote Originally Posted by giteshtrivedi View Post
    Hi,

    Only hot indexes or volatile indexes are needed to be rebuild.Otherwise not needed to rebuild regular.

    Thanks and regards,
    Gitesh
    what utter rubbish, its statement like this that keep this stupid argument going on forever

  6. #6
    Join Date
    Apr 2006
    Posts
    377
    Quote Originally Posted by gandolf989 View Post
    Amazing, it takes 166 pages to say that based on Oracle using a self balancing B-Tree index, indexes should not need to be rebuilt.
    And yet the pages you posted made no mention of rebuilding an index

    Since the OP's question was in regards to rebuilding indexes and not the low-level mathematic algorithms or science behind indexes, careful reading of the doc does mention that rebuilds are beneficial under certain circumstances.

    Nevertheless, even if the doc was 10066 pages, sometimes it is still impossible to dispel the myths that are inevitably perpetuated as evidenced by Gitesh's comment above.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,975
    Quote Originally Posted by ebrian View Post
    And yet the pages you posted made no mention of rebuilding an index

    Since the OP's question was in regards to rebuilding indexes and not the low-level mathematic algorithms or science behind indexes, careful reading of the doc does mention that rebuilds are beneficial under certain circumstances.

    Nevertheless, even if the doc was 10066 pages, sometimes it is still impossible to dispel the myths that are inevitably perpetuated as evidenced by Gitesh's comment above.
    If you understand what a basic binary search tree is, and how performance can improve with the use of self balancing trees, you might realize that with a btree index, the index will end up in the most efficient state. Instead of thinking about indexes as a black box where you have no idea about the implementation, having a full understanding of the inner workings of the index will help dispel the index rebuilding myths that have perpetuated so much over the years. So yes, look at the underlying data structure.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width