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.

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

As a matter of interest, I created table newemp as select * from emp, and then bulked up the rows to 458752. I then did an update newemp set ename = ename || 'SON'. With the index in the same tablespace, it took 29.85 seconds. With it in a different tablespace, on a truly separate physical device, it took 28.95 seconds.

Like you, I repeated the test 6 times, and the difference was sometimes one way, sometimes the other, but always within 1.2 seconds of each other. There was an instance bounce between test to eradicate the influence of caching.

As a final test, I did the following:

SQL> alter table newemp move tablespace indextest; SQL> alter index newempidx rebuild;
[table and index in same tablespace]
SQL> update newemp set ename=ename||'S'; 458752 rows updated.
Elapsed: 00:00:30.08
SQL> rollback;

and then...

SQL> alter index newempidx rebuild tablespace users; Index altered.
[Index now on separate device from table] SQL> update newemp set ename=ename||'S'; 458752 rows updated.
Elapsed: 00:00:30.03

Draw your own conclusions.

Regards
HJR Received on Mon Apr 14 2003 - 16:10:33 CDT