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

"Joel Garry" <joel-garry_at_home.com> wrote in message
> This is where I think you must have been driven nuts by this debate.

I certainly have been. I wouldn't mind if it was just me saying this. Being a nonentity from Sydney, I'd expect a vigorous debate. But Tom Kyte has said it. Jonathan Lewis has. Steve Adams has. Not in quite so black-and-white a way as me, perhaps. But they've agreed with the general thrust of the proposition. Splitting tables and indexes into different tablespaces is one of those 'rules of thumb' that tom hates so viscerally, and has about as much validity as several other 'rules of thumb' that have done the rounds in the past. Which is about zero.

> Aside from the special cases of full-table scans and index-only scans,
> don't you think that there is some contemporaneity between indexes and
> their tables?

No. None whatsoever in fact. No special degree of contemporaneity that arises because one's an index and one's a table, anyway. Of course, if you modify the contents of the table, you have to modify the contents of the index. And that is an issue: that's why my favourite phrase regarding indexes (which they wouldn't let me put in the book because it sounded too gloomy!) is: 'if you can manage without, do'. Because every index slows down DML. But that's because index maintenance costs something, not because there is any inherent contention for the table and its index.

>And in fact, isn't that going to be expected in an
> online system with multiple users - the serialization of each users
> table and index on an insert, for example, would be mooted by all the
> other users inserting at random times? How can you not expect that
> ahead of time?
>

True, but does moving the index into its own tablespace help matters? Intrinsically, not. Oh, it might because presumably this is a new file on yet another hard disk. More spindles must (ordinarily) mean less I/O contention. But you could achieve the same reduction by raiding the single tablespace. Or by creating the single tablespace out of multiple data files.

That's why I keep stressing the word 'inherently' or 'intrinsically'. If the physics of table/index access dictate a serialized approach, then separation can be no cure, since there's no problem. And that basic rule holds whether you've got one insert, or 1000,000. Now, when you have 1,000,000 concurrent inserts, there's a *different* problem of simultaneous access to segments. But that's a different problem, requiring a different cure. Mere seapration of indexes and tables isn't it.

> >
> > 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
>
> Believe it or not, I saw a system with 1000 tables with an option to
> do exactly that. In fact there were 3 options: put everything in one
> ts, put tables in one and indexes in another, or a ts for everything.
> I thought it was hilarious at the time. I spent some time analysing
> what was really needed and wrote scripts for it.

I believe it. However, it only makes sense to do so when each datafile is on its own hard disk. And that's a bit of a tall order for 1000 tables.

> > 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".
>
> What if 500 of us are randomly updating emp and emp_pk_idx? Whether
> you like it or not, there is a relationship between tables and their
> indices. Fairly predictable, in fact. Once upon a time, we had to
> quantify it.

There's no questioning there's a relationship from this end. It's rather the *nature* of that relationship that's at issue here. The common misperception is that they intrinsically contend for I/O. But they don't.

> >
> > Separation of an index from its table doesn't intrinsically help
contention
> > issues at all.
>
> It may or may not, but certainly more likely than two unrelated
> tables.

I totally disagree. If Mary in HR is hammering her payroll application to death, and I'm processing month-end client accounts at the same time, then table HR and table SALES are almost inevitably going to contend. Think up any scenario you want: you *can't* say that because two tables are unrelated, they will not contend or will be intrinsically "unlikely" to contend. You need to know your application(s) and user activity patterns before you can make such a statement with any confidence.

I don't even say that a table and its indexes will never contend, either, because you might be modifying the index whilst I'm starting a fresh update on the table. But that's got nothing to do with the 'realtionship' between that index and table, but arises purely out of the fact that in a multi-user system you may get simultaneous I/O on different *segments*, whatever the nature of those segments.

I think Nuno said it best: deal with contention, not with indexes and tables. I wholeheartly agree with him.

Regards
HJR Received on Thu Jan 16 2003 - 21:05:09 CST