Re: User datafiles and index on separate disks

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 01 Oct 2003 06:19:58 +1000
Message-Id: <3f79e62c$0$13656$afc38c87@news.optusnet.com.au>

Rainer Herbst wrote:

> Howard J. Rogers schrieb:

>> Rainer Herbst wrote:
>>
>>

>>>Peter schrieb:
>>>
>>>>Is it a must to place datafiles, undo segments and table index on
>>>>separate disks? If not, what are the benefits of doing so?
>>>>
>>>>Thanks
>>>>
>>>>
>>>
>>>No, you can have all files on one disk.
>>>The benefit is performance - when e.g. indices and data files are on the
>>>same disk, and oracle will use an index to locate a row, the disk header
>>>should go to the index file first, read some blocks, than go to the
>>>datafile and read the data block. The movement of the header is very
>>>expensive in terms of time!
>>>
>>>Regards!
>>>Rainer
>>>

>>
>>
>> Why will this myth not simply die quietly as it should? There's no
>> performance benefit to separating indexes from tables, and the disk head
>> movement argument is just spurious. On a multi-user system, your disk
>> head will probably move in between reads of rows from even a single
>> table, because of the I/O requests that other users are making. Even on a
>> single user system, your disk head moves between *tables* when you read
>> two or more of them in a single SQL statement (ie, a join): so you get
>> disk head movement, with not an index in sight.
>>

> 
> How many users are working on your database server? I am in the lucky
> position to have only oracle and apache running on the server, so the
> disk heads are not jumping left and right for other users. And spreading
> the files to more than one disk _did_ make a difference! Of course,
> there is no need to separate the indices from the tables, but spreading
> the load on more than one disk normally lead to performance gains.
> 

Come off it, Rainer. I didn't say 'Don't separate things onto more than one disk'. I said 'separating indexes from tables, simply because one's an index and one's a table, is daft'.

Besides, I don't really think your experience of single-user systems is particularly applicable to 99.9% of production databases out there, do you?

And also besides, yes your disk heads will be jumping left and right even in a single user system because, as I said, not even the blocks of a single extent are actually contiguous on disk.

Separate things which contend for I/O. Don't fall into the trap of thinking that indexes and tables inherently contend for I/O, because they (demonstrably) don't.

Regards
HJR Received on Tue Sep 30 2003 - 15:19:58 CDT