Re: Datafiles of tablespace

Philippe,

I have posted this both to yourself and to the group as I am having difficulties in posting to this newsgroup at the moment.  

I would say that it is a bit of both.

A tablespace that is distributed across multiple files can also be distributed across multiple servers or physical drives, reducing concurrent contention.

I think a lot depends on the eventual size of the tablespace and how dynamic it is and what the probable data retrieval pattern is.

For example if 98% of access is against data in a time window of say 1 week and that data all exists at a point in time in a single file, even if this retrieval window moves forward across files, then little gain has been made.

If however the data retrieval spread across the concurrent users is across the data set, then multiple data files have to be beneficial, and you might be considering causing tables in that tablespace to be available for parallel queries, in which you might well wish for there to be no fewer data files than the degree of parallelism.

I'm not sure whether there would an increase of fragmentation in a dynamic database spread across multiple files, but again it depends on whether the prime issue is space utilisation or performance and what business requirements impinge upon both issues.    

I hope this is helpful

regards

Rod Corderey

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

Philippe Mendes wrote:
>
> Someome as tell me that, as far as speed access are concern, that it's
> better to have only
> one datafile by tablespace instead of 2 or more.
> (I'm speeking about datafiles of more than 100 Mb)
>
> Can someone tell me if it is true or if it is an absurdity.
>
> Thanks.
Received on Fri Apr 24 1998 - 04:53:16 CDT