Re: separate tablespaces for tables and indexes

  • From: "Nuno Souto" <nsouto@xxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 15 Dec 2004 07:34:44 +1100 (EST)

On Wed, December 15, 2004 2:21 am, Niall Litchfield said:

> absolutely, I'd be entirely forgetting about splitting objects for
> performance reasons in this sort of case - let the hardware do it.
> (and configuring the hardware appropriately might turn out to be
> someone elses headache!).

How true.  The other thing of course is that every once in a while
the SAN mob wants to re-distribute the LVM/cache allocations.  When that
happens, the DBA better be prepared to accomodate.  This links with
the following, so please stay tuned.

> correct all those years ago. Its the size that is important though,
> not the type of the object. That said, I'm not entirely convinced that
> the ease of maintenance argument holds true either. What maintenance
> tasks does it actually make easier?

Perhaps all of a sudden "size matters"?  ;)

The ease of maintenance kicks in with the point I made above about SAN
re-configuration.  If the SAN mob knows what they're doing - sure,
a rather rare event - there may be a re-allocation of partitioned SAN
cache to different LVMs.  If the DBA has the tables/indexes on multiple
tablespaces, it becomes a no-brainer exercise to allocate them to the
appropriate category of logical device/cache config.  Otherwise, it's
a pain.

Note: when I say tables/indexes, I don't mean *separate* tables and
indexes, I simply mean tables *and/or* indexes.  Following Guy's
advice of separating by size, I'd refine it to include category of
access.  One of the misteries of life for me for example is when I see
instances running multiple applications sharing the same TEMP tablespace.
Why on earth not multiple TEMPs, one for each application?

Then different category tablespaces are sent to the most appropriate
logical device category.  And that's about the extent of it, really.

> You can fragment an autoallocate tablespace enough to get the 'unable
> to allocate free space' message, and you literally can't with an LMT -
> but most folk won't.

You can also end up not being able to load a sorted table, and that
is a bigger problem for me.  But there is a work-around: CTAS.

Nuno Souto


Other related posts: