468,161 Members | 1,972 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,161 developers. It's quick & easy.

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
Jul 19 '05 #1
1 8927
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

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Jeff Boes | last post: by
10 posts views Thread by shsandeep | last post: by
10 posts views Thread by rAinDeEr | last post: by
3 posts views Thread by dcruncher4 | last post: by
1 post views Thread by CK | last post: by
1 post views Thread by simoncole | last post: by
reply views Thread by gcreed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.