Rebuilding Index Tablespace Strategy

[JMac]

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.

[nnigam]

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

[davey23uk]

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

[SANJAY_G]

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

[tycho]

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