Re: separate tablespaces for tables and indexes

On Tue, 14 Dec 2004 09:47:05 -0500, Gogala, Mladen <MGogala_at_allegientsystems.com> wrote:
>
>
> Spreading the disk I/O is not an issue. Majority of cases involves some
> kind of SAN solution, which completely hides any underlying disk structure.
> Your volumes consist of striped and mirrored disk devices cached by the
> SAN box itself. At the time when the DBA sees data files, disk spindles are
> completely indiscernible.

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!).

> The reason to separate is ease of administration of my large objects. I have
> several (3) tables in the XXL tablespace, with EXTENT MANAGEMENT LOCAL
> UNIFORM SIZE 512MB. Indexes are much smaller and are in separate tablespace,

I have elsewhere recommended splitting objects based on size Guy was 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?

> which has allocation policy set to AUTOALLOCATE.

don't like autoallocate myself, but that is *almost* pure prejudice. 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.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l