Re: User datafiles and index on separate disks

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 30 Sep 2003 18:05:27 +1000
Message-ID: <3f793a06$0$1691$afc38c87@news.optusnet.com.au>

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.

Never mind that the blocks of an extent of a single segment are not contiguous on disk (unless you use raw partitions), so that even doing a full table scan your disk head is bobbing all over the place.

So yes, you can separate indexes from tables to eliminate the head movement, only to discover that the head movement happens because of other reasons. You might as well store every single segment in its own unique data file if that's going to be your argument. And don't forget to dump your file system whilst you're at it, because for true sequential access to Oracle blocks involving minimal head movement you're going to need raw.

Sorry Rainer. This one's been done to death, and the argument won't fly.

Regards
HJR Received on Tue Sep 30 2003 - 03:05:27 CDT