Re: How many tablespaces should I have for user data?

You wrote:
>
>1a) Is this how its usually done?

Separation by application is a good idea. Separate development/test tablespace is good; or a completely separate development database is even better.

>1b) Are there any benefits in doing it this way?

It makes life easier when you have to migrate tables, re-create them, manage space and fragmentation, etc. Keeping things grouped logically minimizes the effect of changes on unrelated data.

>1c) Is data retrieval any faster than having all tables in
>one user_data tablespace?
>

You should place tables and their indexes in separate tablespaces, and the datafiles for the tablespaces on separate disks. This will improve performance by reducing contention for the same disk (the disk heads can only be in one place at a time), and allowing concurrent I/O on two disks (tables and indexes are frequently accessed concurrently). Watch the I/O statistics via SQL*DBA "monitor io", and arrange the files so as to balance I/O among all disks.

>than having many small data files within a tablespace. I am just wondering
>is there a standard size to start with? What is a good data file size to
>start with?

Data file size depends on volume of data to be stored in it. You need to estimate the sizes of your tables based on your knowledge of row size and row counts; use the techniques found in the Administrator's Guide, chapter 8.

Usually a single file is best. If you have a very large tables (500+ MB), it can be advantageous to place such a table in its own tablespace, split into several files on different disks. This allows concurrent access to different rows of the same table. It doesn't sound like you have anything that big, though.

One more hint: when you create your database, specify a large value for MAXDATAFILES (I usually use 256). This parameter can be set ONLY at database creation time. The last thing you want is to have to re-create your entire database to add a tablespace or datafile because this parameter was set too low, or left as its default.

Paul Received on Sat Oct 07 1995 - 00:00:00 CET