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

"Joel Garry" <joel-garry_at_home.com> wrote in message

> It may have been done to death, but I think it still doesn't explain
> away what happens when you have many people pounding on the same table
> and index, updating, reporting or both. If you don't separate across
> LUN's or disks, as appropriate, you get hot spots (where the physical
> spot may be extremely elongated due to striping - but the logical spot
> is still hot, and there is a multiplicative effect when two spots are
> "close," by whatever the hardware/software defines as "close"). If
> you just look at it one process at a time, you might as well use MS
> SQL.
Well, I'm not entirely sure what any of that really means, but if you're saying that mulitple users accessing the table/index would cause contention, of course it would. But that's a product of having multiple users, not having the index and table in the same tablespace.

At the end of the day, an index is just a segment like a table is. If you are going to propose that we need to separate one segment away from another because of multi-user contention, then you might as well argue that every table needs to be in a separate tablespace, too. If you update EMP whilst I update DEPT, and we contend, that's two tables contending for I/O. It's exactly the same if I'm updating EMP whilst you update EMP_PK_IDX. Either they all need to be separated, or none do. It's a question of analysing I/O contention issues, not "These are indexes so they go there; and these are tables so they go here".

Separation of an index from its table doesn't intrinsically help contention issues at all.

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

Nope. Sorry to be pedantic. But it's not "to a point", but the entire point. There is ZERO performance benefit to be gained from separating a table from its index(es). Contention is an issue that has to be managed whether the segment is an index, a table, a cluster an IOT or whatever. There is nothing intrinsically contentious about an index and its table. It's all to do rather with what your users update when.

Regards
HJR Received on Mon Jan 13 2003 - 19:32:35 CST