Partitioning and Tablespaces

From: CK <emailckn_at_yahoo.com>
Date: 17 Sep 2003 13:27:51 -0700
Message-ID: <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 Wed Sep 17 2003 - 22:27:51 CEST

Original text of this message