Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tables per Tablespace

Re: Tables per Tablespace

From: David Fitzjarrell <oratune_at_aol.com>
Date: Thu, 07 Dec 2000 15:38:21 GMT
Message-ID: <90oatd$gt0$1@nnrp1.deja.com>

In our last gripping episode bcarignan_at_my-deja.com wrote:
> Is there a standard for determining the number of tables that should
> exist in a tablespace? If I can fit all of the tables for an
> application in a single tablespace, which is composed of a single
> datafile are there any performance ramifications? Other than the case
> of very large (>1 GB) tables, when would it make sense to separate
> tables that logically belong together into separate tablespaces?
>
> Brian
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

There are a number of areas one should consider when placing tables in tablespaces. I cannot possibly list all of them, as some are subjective, but some of the considerations for me would be:

Are large tables being joined on a regular basis? If so, it is probably wise to separate these tables into different tablespaces, preferably on different disks. This is not to say that all large tables that can be joined need to have their own tablespace. But, for some semblance of efficiency the larger (or largest) tables should be divided between two, or maybe three, tablespaces to allow the join queries to return more rapidly.

Are there some static tables, i.e., lookup tables that change very infrequently? These could be placed in a tablespace away from more active tables. This could provide more working space for tables that are undergoing regular data changes by keeping these relatively small tables away from tables needing larger extents that are actively growing.

Are there tables needing large extents, and those that could use smaller ones? Locally managed tablespaces utilizing uniform extents may be a possible solution. This would place tables in different tablespaces depending upon what extent size is needed. Again, there would be no need for a tablespace for each table -- generalize the growth needs of the tables and group them according to those needs. Two or possibly three tablespaces would suffice; one with small uniform extents and one with large uniform extents should work (although you could have one with "medium" uniform extents to hold those tables too large for the small extents but not needing the very largest extents).

Extremely large tables could benefit from partitioning, which could involve several tablespaces to hold the various partitions.

As I stated at the outset I cannot possibly list all of the considerations one could make for your question. I only hope that I have given you some help so that you can better decide on your own how to proceed.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Dec 07 2000 - 09:38:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US