Re: Partitioning and Tablespaces

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" <emailckn_at_yahoo.com> wrote in message news:b5123dfd.0309171227.246048d9_at_posting.google.com...
> 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
Received on Thu Sep 18 2003 - 01:45:49 CEST