Re: separate tablespaces for tables and indexes

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.

Cheers
Nuno Souto
nsouto_at_bizmail.com.au

--
http://www.freelists.org/webpage/oracle-l