Re: Granting table privileges

"Chuck" <chuckh_at_softhome.net> wrote in message

> >> The point you are missing is that a table and it's indexes are often
> >> accessed simultaneously.

> It does happen and my test proves it. This is nothing new. Conventional
> wisdom about I/O since the time magnetic disks were invented says the
> same thing.
>
> I didn't say the index and table were accessed simultaneously. I said
> "together".

If you're going to deny what you wrote, you might do better than leave your original words in a reply! Read the first sentence of this post again, and then tell me you didn't say 'simultaneously'.

But no matter...

>If I access a table block on one cylinder of a disk, then
> immediately afterwards access index blocks on a different cylinder of the
> same disk, I have just done the absolute slowest i/o operation possible -
> physical head movement.

Next, you're going to tell me that a table's extent is contiguous on disk. Or that an Oracle block is. Wrong on both counts. So whilst I don't dispute that head movement is a pain, it's irrelevant to this discussion, because you're going to get head movement whether you like it or not, even reading a table with just a single extent, and no indexes.

Never mind that you seem to be living in a rather odd, though possibly desirable, DBA world where your disk accesses are done in total isolation from the rest of your Users. Fact is, whilst you are fetching your table blocks from a cylinder, I'm fetching my table blocks from a different cylinder. We've just encountered physical head movement, and there's not an index in sight.

>By placing a table's indexes on the same
> *physical* device as the table, I am not only increasing the probability
> of head thrashing, I am *guaranteeing* it!

Strange but true: the moment you have two tables, you have guaranteed the same thing. It has nothing whatsoever to do with one of these segments being an index, but everything to do with simultaneous access to different segments (which happens all the time, unfortunately). Table-table, table-index, index-index... it's the simultaneous access which causes the grief, not the nature of the segment in question.

But since a table, and its index, are never accessed simultaneously during a single DML operation by a single user, then an index cannot contend intrinsically with its own table.

>The results I posted show
> that.

I notice you snipped *my* results. Which showed nothing of the kind.

>I reduced the thrashing somewhat but conducting the test on striped
> logical devices. If I did it on non-striped disks it would have been even
> worse.

Yup, if all else fails, ignore actual posted evidence, and just keep asserting the same thing until the issue goes away!

Fine. I realise that it is never particularly pleasant to be brought up short, and confront the fact that you've been subscribing to performance tuning myths all this time (and Oracle itself doesn't help here, I agree), but it is a myth nonetheless.

Since I usually separate indexes into separate tablespaces as a matter of administrative convenience (different backup schedule, etc), we can leave it there. I have no intention of going round the same old shibboleths evey 3 months or so for every person who missed the original 'Oracle Myths' thread and can't quite bring themselves to believe quantified facts.

You keep doing what you're used to doing, and that's just fine by me.

HJR Received on Tue Apr 15 2003 - 15:36:40 CDT