DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: rebuild index v.s. create new index

  1. #1
    Join Date
    May 2001
    Posts
    285

    rebuild index v.s. create new index

    Since I need to free up some unused space inside a giant tablespace, I basically have to decide if I need to drop the indexes and recreate them after the tablespace being resized, or rebuild the indexes to a different tablespace and rebuild it back after we resized the original tablespace.

    One of the deciding factor here is which one is more expensive -- rebuild index or create new index? If they are pretty much the same expense wise, then I would prefer to do drop and recreate, 'cause if we choose rebuild, we'll have to do rebuild twice (first to rebuild to temp tablespace and then rebuild it back to original tablespace).

    If rebuild is significant cheaper than create new indexes, then I will prefer rebuild as it will offer uninterrupted availablility of the tables, while drop/recreate index will need some down time (unless they won't mind the poor performance).

    What do you gurus think?

    Any input is highly appreciated!

    Elaine

  2. #2
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Rebuilding Index is used so that queries have continuous access to the index while it is being rebuilt. The mechanisms in a Rebuild or a Recreate after Drop is not too different.

    When you rebuild, a new Temp Index Segment is created for the index and afer this new temporary segment is populated, the old index is set to temporary and the populated temp segment is redefined as the permanent segment with the same original index name.
    Sridhar R Patnam

  3. #3
    Join Date
    May 2001
    Posts
    285
    Thanks patnams! Sounds like drop/recreate will be a better option here.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I don't understand why you would have to rebuild it twice, unless you are saying your application hard-codes the tablespace name when it creates objects. Even so, once the objects are created, what's the big deal with them being in a different tablespace?

    Personally, I prefer REBUILD because you can REBUILD ONLINE and not disturb your users. Dropping an index and re-creating it just makes queries slow for the time you are creating and analyzing your index.
    Jeff Hunter

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Rebuild on a usable index is performed by reading the old index (unless compute statistics is invoked, i believe). Recreating it is performed by reading the table, of course ... so would you rather read an index twice, or read the table once?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Feb 2001
    Posts
    295
    Originally posted by marist89
    I don't understand why you would have to rebuild it twice, unless you are saying your application hard-codes the tablespace name when it creates objects. Even so, once the objects are created, what's the big deal with them being in a different tablespace?

    Personally, I prefer REBUILD because you can REBUILD ONLINE and not disturb your users. Dropping an index and re-creating it just makes queries slow for the time you are creating and analyzing your index.
    Also, both CREATE INDEX and REBUILD (without ONLINE clause) will acquire locks on base tables, and your users WILL complain if the operation is done during the day.

    With REBUILD ONLINE, the same lock is acquired just for a moment, and the rest of the operation is done on another separate temporary segment. After rebuild is complete, the new segment replaces the old one. The worst possibility here is some degradation on performance, not downtime.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    Rebuild on a usable index is performed by reading the old index (unless compute statistics is invoked, i believe)
    And also unless Online is invoked..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    I agree with Adriano, online rebuild is the best option. Provided u needs to have a enough space for additional index object to be created. Online requires no downtime, but always better to do it on low activity or in maintenance window.

    Adriano, all the best to Brazil for copa america !!!!
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

  9. #9
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Watch out for Partition tables if you are rebuilding indexes in peak business times.
    Your indexes go for Unusable state while you rebuild and users will be having interruptions in their activities, depending upon which partition data they are working on and especially Primary/Unique Keys of Partition tables.
    Reddy,Sam

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