Re: Question on tablespace/file allocation

"Marcia Thomasson" <marcia_at_nospam.solcominc.com> wrote in message news:3b83d51c_at_nntp01.splitrock.net...
> > >
> > > I have gone through the excercise of sizing each table and allocating
> the
> > > appropriate amount of space for it when it is created, and I have also
> > added
> > > up the total amount of space I need for each tablespace and created
the
> > > tablespace with that amount of space in two files (with the amount
> divided
> > > by 2). I have also done this for my indexes.
> >
> > Sizing the tables is a good exercise, but what do you mean by
'allocating
> > the appropriate amount of space'. If you just mean make sure the
> tablespace
> > is bigger than the total size of the objects to be stored in it then
that
> is
> > good. If however you mean create the table with an initial extent equal
to
> > the expected size of the data then that is bad. You should be using
> uniform
> > extent sizes within a tablespace.
> >
>
> Why is it bad to create the table with an initial extent equal to the
> expected size of the data?

Because you will have extents of different sizes, and that will lead to tablespace fragmentation. Never mind that most of that space will lie there empty until the table eventually fills up.

>The potential sizes of my tables could vary
> widely within a tablespace, why would I want to allocate the same extent
> sizes for all of them? Wouldn't that lead to either a) way too much space
> being allocated for some tables (if I err'ed on the "large" side) or b)
> tables being fragmented (if I err'ed on the "small" side)?
>

Tables don't become fragmented. Tablespaces do. And odd-sized extents is the principle cause. And yes, you're right: if you've one DATA tablespace, and try and keep extent sizes within it the same, you're going to end up allocating way too much space to some tables. That's why you'd create *several* DATA tablespaces -one with (say) 128K extents, one with 1Mb extents and so on. House the right table in the right tablespace, and the over-allocation issue is gone.

Frankly: you are running 8.1.6, so you should know about locally managed tablespaces. In LMT's, we couldn't give a monkey's about how many extents a segment comes in. And we guarantee consistent extent sizes, and thus eliminate tablespace fragmentation. And we get some performance improvements, and some possible contention on the data dictionary eliminated. So, if you're not using them, you should be (with due care for sizing of the extents in each one).

And the old myth about having to size a segment to consist of just one extent was always just a myth -and, with LMTs, is even more of a fairy story.

Regards
HJR
>
>
>
Received on Fri Aug 24 2001 - 21:02:14 CDT