Re: restructing tables in new tablespaces - advice

"Chuck" <chuckh_at_softhome.net> wrote in message news:Xns93D47FFC912A0chuckhsofthomenet_at_130.133.1.4...
> "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.
>

Hi Chuck,

Separating segments by size as you suggest made sense with DMT but in the not so new world of LMT, it's kinda redundant. But it's still a legacy that refuses to go away, probably because it's still being hailed as necessary by (so-called) experts and probably because of Oracle itself with regard to how it manages LMT with the autoallocate option using a similar algorithm. Having a tablespace with a uniform extent of 1M and a tablespace with a uniform size of 50M suggests that somewhat over 50 extents is an issue (why else separate them and why else go to the considerable trouble of moving a segment from one tablespace to the other when you get it wrong). And of course having an issue with 50 extents is a nonsense, as it is with 500 extents, as it is with 5000 extents...

In fact if you can show me a segment that has an issue with the *number* of extents, I'll show you a segment that should either be partitioned (preferably) or a segment that should be stored in it's own tablespace.

From a reporting point of view, it's not too hard to monitor segments that have unexpected growth characteristics or tablespaces that have space issues or to write a report exactly as you describe. It really isn't. Not if the tablespaces themselves are reasonably sized.

It really is time someone wrote a paper called "How to Stop Fragmentation and Start Living In the 21st Century" !!

There's a thought ;)

Cheers

Richard Received on Tue Aug 12 2003 - 05:04:13 CDT