Rebuilding Index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Rebuilding Index

  1. #1
    Join Date
    May 2001
    Location
    Delhi
    Posts
    341
    Hi,

    Whatis difference between

    - Rebuilding index online

    - Dropping index and then creating index again

    Regds
    Vijay
    --------------------------
    The Time has come ....

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Rebuilding the index online keeps an index available while the rebuild is running. Drop/Create doesn't let your queries use an index while it is being created.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Plus rebuilding is much quicker than drop+create - it usually involves substantialy less I/O (because the old index is usually much smaller than the table) and requires no sorting (as the indexed keys are allready sorted in the old index).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    May 2001
    Location
    Delhi
    Posts
    341
    Hi,

    Thanks,

    Does it has to do something with the Space.

    Means it frees space or something ?

    Vijay
    --------------------------
    The Time has come ....

  5. #5
    Join Date
    Apr 2001
    Posts
    219
    Deletes from a table are not removed from an index. So, yes it does free up space.
    ______________________
    Applications come and go,
    but the data remains!

  6. #6
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi,

    It gives space back but first it builds a new index using the old index so you need space for both of them. When it finishes it drops the old one.

    The best way to deal with rebuilding indexes is having a localy managed tablespace with a uniform extent size. This will help to get rid of fragmentation cause all extents will have the same size.

    In practice I have seen indexes having only half their original size after the rebuild.

    Tycho

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