Re: My indexes are breaking things...

Understand what tablespaces are supposed to do: keep I/O separate, and group similarly-behaved segments together. Then the question answers itself.

No, you don't just bung 100 tables into a single tablespace. You ask yourself what the sizes of the tables are. It would be madness to lump together a 100Gb table and a 16K table. Then you ask yourself what the growth habits of a table are. One that grows by 100M per day shouldn't be within sight nor sound of one that acquires an extra row per month.

Then you recall that the unit of backup and recovery is the tablespace/datafile. So STATES_OF_AUSTRALIA, which contains about 7 rows and hasn't altered in 100 years (but we await the 'insert into S-O-A values ('New Zealand');' with eager anticipation) shouldn't be in the same tablespace as 'DAILY_SALES'. One needs backing up daily, one needs backing up about once a year.

The you recall the dictum that ALL segments within a given tablespace should have identical extent sizes (a point related to the size issue mentioned above). Otherwise you risk tablespace fragmentation. And with locally managed tablespaces, you don't have much choice about this anyway.

Then you remember that on Metalink, there's a paper called something like 'How to stop worrying about fragmentation and start living', which proposes as a response to all the above issues, creating half a dozen or so tablespaces, each locally managed, and with a spread of extent sizes, such as 64K, 1M, 8M, 64M. And you house the appropriate tables in the appropriately-sized tablespace. And then you can stop worrying about them.

But you might want a couple of 64K-extent tablespaces, a couple of 1M-extent tablespaces and so on because of the I/O issues you identify... Just because two tables are both candidates for 1M-extent treatment doesn't mean they should reside in the same tablespace if your application dictates that they are both read from and written to simultaneously.

Regards
HJR "Ramon F Herrera" <ramon_at_conexus.net> wrote in message news:c9bc36ff.0208071039.11860fcc_at_posting.google.com...
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > I would never ever consider putting each table in a separate tablespace.
> >
>
> Sybrand, you just saved me a posting. I was wondering about
> that issue myself. So we agree that it is not wise to put
> my 100 tables in 100 different tablespaces. Should I put the
> 100 tables in just 1 tablespace? Perharps have 2 o 3 tablespaces,
> each in a different disk?
>
> Thanks,
>
> -Ramon
Received on Wed Aug 07 2002 - 17:32:29 CDT