Re: Granting table privileges

From: Chuck <chuckh_at_softhome.net>
Date: 17 Apr 2003 18:09:29 GMT
Message-ID: <Xns936090048BACDchuckhsofthomenet@130.133.1.4>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in news:IUZma.15169$1s1.240306_at_newsfeeds.bigpond.com:

> "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'.

My mistake and I apologize. I would never argue that a table and it's indexes are accessed simultaneously by the same SQL. It won't happen. Not sure what I was thinking when I wrote that. Probably meant to edit the article further before posting it. What I meant to say is that they are often accessed together meaning index block, table block, index block, table block, etc. The point I am trying to make is that if you have those two segments on the same physical disk you're going to be banging the disk head all over the place. Especially if the segments are separated by a large number of cylinders.

> 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.

Depends on how the files and segments were created. It is entirely possible to make segments contiguous on disk if you start with a clean disk and either preallocate your extents, or have only one object per tablespace on that disk (assuming tablespaces were properly sized and do not autoextend).

>
> 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.

Of course you create head movement in that scenario, but you can still minimize it by proper placement of segments. Or inversely, you can guarantee additional unnecessary head movement with poor segment placement. My argument has never been about eliminating head movement. That's impossible unless you're using solid state disk. It's been about keeping head movement to a minimum.

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

Ditto. We've beaten this issue to death. I say we agree to disagree until either of us in convinced otherwise. As always, I appreciate your advice. I'll also see if I can hunt down that entire Tom Kyte article you mentioned in another post.

Chuck Received on Thu Apr 17 2003 - 13:09:29 CDT