Re: restructing tables in new tablespaces - advice

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 Received on Mon Aug 11 2003 - 17:05:32 CDT