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: Howard J. Rogers <howardjr_at_www.com>
Date: Fri, 8 Dec 2000 06:33:32 +1100
Message-ID: <3a2fe614$1@news.iprimus.com.au>

"Tony Walby" <tony.walby_at_bridgewatersystems.com> wrote in message news:3A2FAFA7.45BB4DB8_at_bridgewatersystems.com...
> Are you aware that a tablespace can have multiple datafiles. I do not
> believe there is a limit on the number of table sin a tablespace. Actually
> it is recommended that you keep all you app tables in one tablespace,
> separate you indexes into a separate tablespace of course and you could
> also separate you add-on appdata to another tablespace.

It most certainly *isn't* recommended that you put all an application's tables into a single 'DATA' tablespace. You will fragment like crazy (potentially), and you will have performance bottlenecks a-plenty, as competing updates to different tables fight for the i/o, if their extents happen to end up on the same datafile(s) within your tablespace.

The whole concept of locally managed tablespaces indicates what the real recommendation is: you should have consistent extent sizes within a tablespace, and accordingly group tables into tablespaces depending on their storage and growth requirements.

Even then, two tables which need similar-sized extents, and which grow at the same sort of rate, should not be placed inside the same tablespace if they happen always to be updated simultaneously, or queried together.

And whilst you could theoretically construct things such that multiple applications are housed within a single database, each separated from the others by using different tablespaces, it's not what tablespaces were really designed for, and imho is a performance nightmare in the making.

Regards
HJR
>
> Generally you should keep your datafile under 2G (Could run into Oracles
> 2G limitation on some platforms and versions).
>
> Here is what I do.
>
> Tablespaces Datafiles
> system system01.dbf
> temp temp01.dbf
> usr usr01.dbf
> rollback rollback01.dbf
> rollback02.dbf
> rbs_big rbs_big01.dbf
> app_[appname]_data app_[appname]_data01.dbf
> app_[appname]_data02.dbf
> app_[appname]_index app_[appname]_index01.dbf
> app_[appname]_index02.dbf
> tools tools01.dbf
> misc misc01.dbf
>
> Should be self explanatory.
>
> 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.
>
Received on Thu Dec 07 2000 - 13:33:32 CST

Original text of this message

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