Partitioning and Tablespaces

[CK]

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

[Tim Cuthbertson]

It all depends on how you are processing the data. If you rarely access any
but the current day's data, then you don't have to separate the tablespaces
for performance reasons. On the other hand, if you are always simultaneously
accessing many days' data, then the partitions should be on separate
tablespaces and, as much as possible, on separate disk volumes.

If you use locally managed tablespaces with uniform extent sizes,
fragmentation of free space will not be a problem. For 2 GB data in each
partition, you could make the extent size about 64 MB or 128 MB with no
problems. If you manually manage dictionary tablespaces, make sure your
INITIAL and NEXT sizes are the same with PCTINCREASE 0.

Tim

"CK" <em******@yahoo.com> wrote in message
news:b5**************************@posting.google.c om...

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