Partitions each in a separate tablespace ?

Friends,

I am creating partitions on a table, and need to decide whether to put partitions in separate tablespace (a tablespace for each partition) or to put them all together.

Here is my reasoning:

Main advantages of partitions is better performance, and easier maintenance.

As far as performance goes in my case only the last (most recent) partition will be used by the application, so there is no big advantage of spreading the load between different tablespaces (btw my database is on a filer so I am not spreading load between physical disks anyway). As far as maintenance goes, for dropping of the old (historic) partitions I don't see any difference whether they are in separate tablespaces or in the same one. As far as a possible recovery (file corruption, etc.) goes it seems to me that for data partitions there is just a minor advantage of having each partition in separate tablespace, so that only that one corrupted partition datafile has to be copied for the backup, instead of a bigger datafile (for all partitions). As far as index tablespaces go there might be a significant difference if one of the local index partitions is corrupted, only that index need to be rebuilt, comparing to rebuilding all the local indexes in case they are all in the same partition.

So I am inclined to put index partitions each in a separate tablespace, and I am not sure about data partitions.

Any other thoughts, experience, suggestions ? Received on Tue Dec 12 2000 - 20:46:17 CST