Re: restructing tables in new tablespaces - advice

"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. Received on Mon Aug 11 2003 - 11:34:54 CDT