Re: how many tablespaces

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<Tly1c.112729$4o.138921_at_attbi_s52>...
> "Vissu" <vissuyk_at_yahoo.com> wrote in message
> news:2bedd6a7.0403032012.2899a4a6_at_posting.google.com...
> > Hi ,
> >
> > We will have about 50 tables. Some tables will be huge (severel
> > million rows) and some are small but most of them will have about a
> > million rows. For best performance of the database, how many
> > tablespace should I have. I was thinking of using Locally Managed
> > tablespaces (uniform extents) for both data and indexes.
> >
> > Can anyone share some experiences? Any other tips on layout of the
> > database will be appreciated. Thanks
> >
> > Vissu
> What version? Use locally managed with auto managed extents and put the
> data all in one tablespace.
> Jim

How many tablespaces you should use to support your database depends on the expected size and number of your objects and the organization of your disk farm.

I greatly favor organizing most applications into large and small object tablespaces. Generally two sizes will work for everything. The best sizes depend on the objects involved.

I do not like autoallocate because it still can suffer from the free space fragmentation problem while a properly sized uniform extent tablespace will never suffer this problem. The only time I think autoallocate is useful is where you install a canned package and the vendor places everything into a single tablespace (usually the application owner default tablespace).

It all comes down to judgment on which arrangement will provide the most effective managment options.

IMHO -- Mark D Powell -- Received on Thu Mar 04 2004 - 16:33:57 CET