Re: Granting table privileges

From: Chuck <chuckh_at_softhome.net>
Date: 15 Apr 2003 15:40:42 GMT
Message-ID: <Xns935E76CCED1C8chuckhsofthomenet@130.133.1.4>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in news:viFma.14411$1s1.236979_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.

>
> They are *never* accessed simultaneously. Update a table, and the
> table is updated and *then* the index is modified. Serialised
> operations do not, and cannot, conflict.
>

>>Read the following carefully. I am not saying that
>> separating all indexes from all tables is automatically a good idea.
>> I am saying that separating a table for *it's own* indexes is a good
>> idea (and so does Oracle tech support, Oracle University, etc.).

>
> They're wrong. They write that because they are pitching at simple
> solutions to diverse problems.
>

>> The reason is there
>> is a high probability that these segments will create disk contention
>> if they are on the same physical devices because they are often
>> accessed together.

>
> Please show me how an index is *ever* accessed simultaneously with its
> own table by a single piece of DML. It doesn't happen.

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 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. 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! The results I posted show that. 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.

>

>>It's not that hard to figure this out, nor to prove it. Before
>> automatically contradicting this, please test your theory. Do a
>> couple of simple tests and see what executes faster.
>>
>> 1. Create a new empty table
>> 2. Create an index on that table in the same tablespace as the table.
>> 3. Insert 500k rows into it and record the elasped time.
>> 4. Drop the index
>> 5. truncate the table
>> 6. Create the index in a separate tablespace on separate physical
>> drives. 7. Insert 500k rows again and record the elapsed time.
>>
>> I repeated the above test 7 times. The insert where the table was
>> physically separated from the index ran consistently 15-20% faster.
>> The volumes involved were all raid 0+1, 3 physical drives, 128k
>> stripe size. These are facts and actual measurements, not theories.

>
> Neither are my statements.
>
> But I'm not going round this old chestnut yet again. When Jonathan
> Lewis, Steve Adams and yours truly tell you that an index can't
> contend with its own index, it's not because we sit there deep in
> thought, but because it's been tested many, many times.
>
> In any case, your description of your test is inadequate: if you've
> only got the one raid 0+1 volume, then you haven't even physically
> separated a table from its index, since the essence of raid is that
> you haven't a clue where data ends up.

I guess I didn't make the hardware involved clear. There were multiple logical volumes. Each was an identically configured raid 0+1 device striped over three physical disks so the segments were indeed phyically separated. I will conduct the same test again today on the same server this time with unstriped volumes. If my assertion is correct, I should see an even greater desparity in the update times. Received on Tue Apr 15 2003 - 10:40:42 CDT