Rebuilding Index Tablespace Strategy
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rebuilding Index Tablespace Strategy

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Rebuilding Index Tablespace Strategy

    My index tablespace has been growing for 2 years. I do check it and rebuild individual indexes as and when, generally reducing their size by around 2-5%.

    The datafiles have autoextend OFF. So when the Tablespace reaches between 97-99% full, I add another datafile.

    I now have 7 x 100Mb Index datafiles. They are all DICTIONARY Managed - this is a legacy of the original pre-Ora8i database design.
    We now run Ora 8.1.5.0 on NT4.

    I want to completely rebuild these indexes - all of them.

    I was planning to create a REBUILD_INDEX tablespace with 1 datafile - of say 250Mb - with AUTOEXTEND ON.

    Then I was going to rebuild ALL the indexes into it.

    Then I was going to drop and recreate the original tablespace.

    Then I was going to REBUILD all the indexes back into this tablespace.

    I do like the existing individual 100Mb datafiles we use as its a good graphic visualisation of the growing tablespace. Is it best to build them all back into just 1 datafile? if so, what happens if the 1 big datafile becomes corrupt? Wouldn't it be better to use several smaller datafiles (For recovery purposes)?

    If individual datafiles are better - how do I build the indexes back into 5 or 6 100Mb datafiles like before.

    Is there a better way to accomplish what I'm trying to achieve?

    Advice - comments - criticism all welcome.

  2. #2
    Join Date
    Oct 2001
    Posts
    11
    My thought would be -

    1) keeping multiple datafiles will make sense only if you could span these files across several disks. this will give faster disk i/o.

    2) Keeping them in separate datafiles or single datafile will hardly make a difference in case of corruption as it is recommended to take the complete index tablespace offline and recover. As partial availability leads to strange problems.

    Hope it helps,
    Regards,

    - Niranjan

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    If individual datafiles are better - how do I build the indexes back into 5 or 6 100Mb datafiles like before.
    This can be done by simple creating your original tablespace and adding datafiles as necessary.

    Not a big fan of autoextend on, you could find youself in trouble if something goes wrong and your filesystem becomes full quickly

    Would also make the new tablespaces locally manage with uniform extents

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    If tablespace name doesn't matter to you then you don't need to re-rebuild the indexes after rebuilding them once to the new tablespace.

    Sanjay

  5. #5
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    rebuild

    Hi,

    The number of datafiles is not a big issue if you have a lot of datafiles you only have to change maxdatafiles and/or db_files once and a while.

    My advice would be give all indexes the same extent size (and pct inc = 0) or better use a LM tablespace with uniform size.
    This would enhance the rebuild which can be done in the tablespace without causing fragmentation.

    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