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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:akHT9.20745$jM5.57153_at_newsfeeds.bigpond.com...
>
> "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
> >
> >
>
>

If Oracle's own instructors (not all probably) are still teaching that it is better for performance, then the myth wont go away anytime quickly Received on Fri Jan 10 2003 - 17:28:09 CST