Re: Granting table privileges

"Chuck" <chuckh_at_softhome.net> wrote in message news:Xns935A61CE93DDchuckhsofthomenet_at_130.133.1.4...
> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in
> news:3E93CCEE.6ABF_at_yahoo.com:
> er getting sole access to the devices involved.
> >
> > Nowadays you've got sort-merge, index full scan, fast full scan, 100's
> > of users, storage abstracted out to n-layers, file system read-ahead,
> > the list goes on. The odds of getting the benefit of reference
> > locality (ie the next physical op picking up right where the previous
> > one left off) is virtually nil, possibly with the exception of redo
> > logs. Thus the separation of data and indexes is probably a moot
> > issue
>
>
> I would disagree with this.

Sorry to say this. But there is NOTHING to disagree with. This isn't a matter of religious belief, or 'I've been a DBA for 20 years and you haven't'. This is a matter of simple fact, and elementary physics. Indexes and tables contend no more than an index with an index, or a table with a table. End of story, and hence 'reflex' separation of indexes and tables is simply mindless DBAing, and achieves nothing.

>There's no cost involved in separating indexes
> from their tables

Yes there is, actually. The cost is that you think you've done your job. You sit back, with a warm glow, on the grounds that you have dealt with contention by separating indexes from tables. Yet you haven't, and you've been lead into a false sense of achievement. That can be a serious cost.

>and there still is the possibility that doing so can
> reduce disk head contention.

No. Think carefully, please. Separating any segment from any other segment might reduce disk head contention. But separating indexes from tables, in and of itself, reduces NO segment contention. Of course an index and a table might contend. But so might a table and another table, or an index and another index. Look at where the contention is, not at the fact that things might be indexes or tables, clusters or IOTs. You simply, as a matter of plain fact, cannot say that an index is any liklier to contend with a table than any other segment. The physics is against you if you insist on saying it nonetheless.

>Sure, we've got many more join operations now
> than just nested loops, but the optimizer still picks that at times and
you
> still will have times where the index blocks and table blocks that you're
> after are on the same spindle regardless of how much striping is involved.

Please think about this a bit more. The issue here is that you have segments on the same spindle. What on earth makes you think that it is intrinsically worse to have Table A and Index A on the same spindle than to have Table A and Table B?

> RAID, storage abstraction, and new join operations have certainly reduced
> the possibility of disk head contention, but they have not completely
> eliminated it.

I agree. But this has absolutely zilch to do with anything being an index or a table. Talk segments, and you will see that there is NO performance gain to be achieved through mindless separation of tables and indexes.

But this has been dealt with in a very, very extensive thread about 6 months back. So I'm not going to get into a long-winded discussion on the issue.

Regards
HJR Received on Sun Apr 13 2003 - 03:03:10 CDT