Re: IO question

Keith-

Your are simply wrong here. There are no circumstances under which a table and its index are accessed at the same time within a SQL operation. Each is a distinct IO operation and if you have optimized extent sizes to the operating system IO chunk size you will be just fine. Moving back and forth between table extent and index extent is basically random with respect to head position. Moreover, when you have multiple concurrent users, the randomness of IO is such that the only consideration is the overall IO response of that drive, not head positioning. Spread IO based on actual IO contention, not logical relationships between objects.

Moving an index away from its table may or may not help performance. It is a matter of IO concurrency that dictates this, not any logical relationship between the index and the table. The important thing is how many other users are hitting the same objects at the same time. As such, the only effective way to tune IO is to spread objects over many disk drives - whatever the objects may be. Measure IO contention at the file level, move the busiest objects to other drives.

Better yet, use the SAME method and eliminate both performance and administrative issues at the same time.

• ricky

Keith Boulton wrote:
> There is a considerable opportunity to get disk head contention if indexes
> and data are on the same disk as the head moves from index to table and back
> to index. Traditionally this was an important issue. It may not be such an
> important issue now because of the existence of high cache hit ratios and
> the improvement in device speeds. To some extent, a RAID 5 system reduces
> the extent of the problem because e.g. in a 5 disk array, the head only has
> to move 1/4 of the distance it would move on a single disk.
Received on Wed Jan 30 2002 - 09:19:25 CST