Re: Separating data, index objects

"Mark A" <nobody_at_nowhere.com> wrote in
news:Hu-dnXABzv0PSVTfRVn-qg_at_comcast.com:

>
> "IANAL_VISTA" <IANAL_Vista_at_hotmail.com> wrote in message
> news:Xns9689B06D3450ESunnySD_at_68.6.19.6...

>> "Mark A" <nobody_at_nowhere.com> wrote in
>> news:epadnVAeuImRXlTfRVn-rQ_at_comcast.com:
>>
>>>
>>> In theory, if the table data and index data are in the same
>>> tablespace, the disk contention could occur if there was a
>>> significant amount of I/O against the table and index at the same
>>> time. 
>>
>> As a generalization the above is true.
>> However when Oracle decides to use an index, first the index is read
>> and then then table is read. So for any single transaction ( without
>> parallelization) the index & table are never read "at the same time".

HUH?
"separating the two probably applies more to Decision Support than OLTP." Based upon which metrics?

How in the world did you conclude what I described was OLTP & not DSS?

Assuming high transaction rate against an index, please explain how having the index in its own tablespace "it could make a difference.". After the block has been read from disk, the data will be in the SGA if it needed by another session (assuming it has not been aged out). If a different block is requested from the same table or index, what difference does it make in which tablespace it resides?

Yes, more spindles can sustain more I/O requests per second, but on a per spindle basis when the maximum rate is hit, the I/O requests start to queue.

It would be nice to see actual reproducable metrics, rather than unsubstantiated generalities. Received on Mon Jul 04 2005 - 19:56:29 CDT