Re: Locally Managed Tablespaces ... again!!!

"David Sharples" <david.sharples3_at_ntlworld.com> wrote in message news:YlFT9.171$p_1.1699_at_newsfep4-win.server.ntli.net...
>
> "Charlie Edwards" <charlie3101_at_hotmail.com> wrote in message
> news:217ac5a8.0301100657.43251985_at_posting.google.com...
>
> >
> > I get transferred to another project and, lo-and-behold, we're back to
> > Dictionary Managed Tablespaces (sigh), with tables and indexes in
> > separate tablespaces (double sigh).
> >
>
> On my recent Oracle DBA Admin Part 1 course (8i) I was told to keep
indexes
> and tables in seperate tablespaces
> to group segments of similar type together. Is this wrong now?
>

It's not wrong, exactly. But it's pointless if you're doing it for the "old" reasons. The Oracle course doco. for performance tuning *still* mentions putting tables and indexes into seaparate tablespaces because it avoids I/O contention when the table is updated. And that's the old myth which is still very prevalent.

As was done to death here a while back, it's simply not true. When a table is updated, the index maintenance activities are *serialized*, so that they take place after the table update. Meaning that there is *no* I/O contention. So putting indexes into a separate tablespace for some performance gain is a complete waste of time.

However, it still generally makes sense to split them. Do you feel the need to back up your indexes at the same rate at which you back up your tables? After all, you could always just re-create the indexes if need be. But in the same tablespace, both tables and indexes must be backed up at the same time.

If you want to perform maintenance on a data file, you offline the tablespace. If you offline a tablespace containing both indexes and tables, no-one can do any work at all. In separate tablespace, you could offline the index one, and people could still get at their data, albeit rather more slowly than they're used to.

And so on. Splitting them is a *management convenience*, not a path to better performance.

Regards
HJR
> Dave
>
>
Received on Fri Jan 10 2003 - 16:06:37 CST