Re: Granting table privileges

From: Chuck <chuckh_at_softhome.net>
Date: 14 Apr 2003 18:17:20 GMT
Message-ID: <Xns935D915A39B63chuckhsofthomenet@130.133.1.4>

"Howard J. Rogers" <howardjr20002_at_yahoo.com.au> wrote in news:0H8ma.13293$1s1.217525_at_newsfeeds.bigpond.com:

>
> "Chuck" <chuckh_at_softhome.net> wrote in message

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

The point you are missing is that a table and it's indexes are often accessed simultaneously. 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.). 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. 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. Received on Mon Apr 14 2003 - 13:17:20 CDT