Re: restructing tables in new tablespaces - advice

quarkman wrote:

> On 11 Aug 2003 16:34:54 GMT, Chuck <chuckh_at_softhome.net> wrote:
>
> > "Steven" <SPAMBLOCKER_at_gmx.net> wrote in
> > news:bh8f0u$v69sf$1_at_ID-82797.news.uni-berlin.de:
> >
> >> Hi,
> >>
> >> I have to restructure the tables in our database and am wondering the
> >> best way to structure the tables. Current the tables are ordered by
> >> application in separate tablespaces. But I now want to use UNIFORM
> >> tablespaces. Thus I am wondering if I should separate the tables by
> >> size as some tables are 1-2 MB and other 80-300MB. does it make sense
> >> or not worth the effort??
> >>
> >> Does anyone have any advice on this??
> >>
> >
> > Definetely separate by size. I typically create tablespaces with names
> > that reflect the extent size like data32k, data1m, data50m, index1m, etc.
> >
> > If you size your extents based on how fast the segment grows, it will
> > also help you predict when the tablespace will need to be extended. If I
> > have a segment that grows at 1m per day, I would make the extent size at
> > least 4m. That way I can run nightly reports showing which segments
> > cannot extend and when that segment appears on the report I know I've got
> > at least 4 days before it's going to fail.
> >
> > Additionally you may want to separate by other factors. For example, some
> > people still like to separate tables from their indexes even though
> > recent evidence suggests it really doesn't yield much performance benefit
> > on SANs.
> >
> > I also like to separate apps and logically separate parts of apps into
> > different tablespaces. If I need to offline or recover a tablespace I can
> > do it with minimal impact.
>
> I think you missed the essential point of tablespaces in that reply (though
> it was otherwise sound): separate things out which are likely to contend
> for I/O. It's no good just saying "all these tables are small, so I'll
> stick 'em in the 64K-extent-sized tablespace" if, in the process, you
> happen to co-house half a dozen tables which are forever being hammered to
> death simultaneously. Sure, separate by size... but then make sure you
> haven't introduced I/O hotspots in the process.
>
> ~QM

Where's Howard Rogers when we need him?

Everyone is correct ... it is a very complex topic ... and everything must be considered.

I am of the belief that tablespaces should be size and application specific. And the I/O problem best solved by SAME or creating arrays involving multiple disks that spead out the I/O. To try to manually do it on a tablespace-by-tablespace basis is a losing proposition.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)