Re: Separating data, index objects

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

Yes that is true, and separating the two probably applies more to Decision Support than OLTP. But in an OLTP environment like you described, there could be a high transaction rate on the same tables and indexes, where it could make a difference.

But the real answer is to get as much as possible to be cached in the bufferpool, especially the indexes. I personally think that people put too much emphasis on disk performance while ignoring maximum use of bufferpools (people should spend less on disk and spend more on memory). Obviously, disk performance for logs is an exception, and is high performance disk is very important because log writes can be synchronous with commits.

Disk performance is also more important in Decision Support systems where the amount of data will always be much larger than the amount of memory available for bufferpools. This effects both complex queries and loads. Received on Mon Jul 04 2005 - 19:30:09 CDT