Re: Sizing of a Datafile

David Parry wrote:
>
> When first creating a table what should you use to determine the size of
> the datafile, and does it matter how big you make it because you can
> manually alter it anyway?
>
> David Parry
> dparry_at_nu-pharm.com

In general tables should be independent of datafiles. First, you have a maximum number of datafiles which is usually not very large -- for example on my NT 4.0 configuration the max is 20 :-( -- and in a typical data base you will probably have hundreds of tables. Second, depending on your operating system a datafile may be required to fit on one disk volume which, unless you are prepared to use RAID with striping may prove to be a killer.

The best scheme for setting up datafiles that I have come across is to organize your tablespaces so that each consists of one datafile and have the tablespaces associated with table sizes rather than applications or physical devices. For example, I just reconfigured my tablespaces to TBSP10K, TBSP100K, TBSP1M, TBSP10M, and TBSP100M which respectively consist of tables with initial and next extents of 10K, 100K, 1M, 10M, and 100M (its a data warehouse). The advantages of this scheme are twofold: 1) you won't have very many datafiles, and 2) since every extent in every table in a tablespace (and therefore in the same datafile) is the same size your tablespaces will never become fragmented.

Jerry Received on Wed Sep 03 1997 - 00:00:00 CDT