Need a piece of advice on allocation of tablespaces for partitioning
We are using a day level range-based approach as our parititiong
scheme given that we have data inflows running into 15 million rows
each day averaging around 2GB. The table has 31 partitions (one for
each day) as we do not plan on retaining the data beyond 2/3 weeks.
The partitions are purged at the end of 2/3rd week depending upon the
when it is scheduled to run and how long the data is retained.
My question is on the number of tablespaces that i need to create to
hold this data. Is it ideal to create just 1 tablespace (60GB) for the
entire table/parititions or 5 tablespaces - 1 for each week. We thus
have all the daily partitions for the week assigned to one tablespace.
Would repeated truncation of partitions, followed by data population,
cause fragmentation to the tablespace data files?
Need some advice here...
thanks
CKN