Re: Index management

"Richard Foote" <richard.foote_at_tbigpond.nospam.com> wrote in message news:VkOkc.7206$TT.2330_at_news-server.bigpond.net.au...

> Firstly, having consecutive blocks in an extent doesn't necessarily mean
> that they have to be consecutive on disk. There may be many reasons why this
> may not be the case.

Of course. Not the least of which might be the file system in use in that same disk. Always had a chuckle when I saw recommendatins to "defragment" tables in NT servers when NTFS by default will ensure allocation segments are NOT contiguous!

> Remember, we read one leaf block, then read hundreds of table blocks, read
> the next leaf block, read hundreds of tables blocks, etc.

It all seems to point to the table's "clustering factor" being much, much more relevant, isn't it? BTW, the reason I'm using ""s is that I don't see it really as a clustering of the table but more a physical serialization of rows with same and/or consecutive values on the relevant columns.

Don't know if there is a better term to describe this. Clustering to me implies the keeping together of rows with same value of keys, which is only partially the need here. I also want the next value of the keys to be physically close to the previous value. All this to make sure that I minimise the I/O on a range scan on those same values. Am I explaining myself well?

> This is "contention" is highly probable while processing one leaf block and
> waiting to move onto the next. The chances that an entire index range scan
> can be performed uninterrupted from contention on the device by any other
> processes is almost certain zero on any remotely multi-user environment.
> This is the key point. We are not the only user process on the database, nor
> the only process wanting to use that same physical device.

Good point. I'm reminded of the arguments for/against the SAME technique a coupla years ago or so. In my mind, what I want is an averaged disk I/O wait time, without hot spots. Which multi-user gives me a lot of and SAME helps with even more.

Given a typical multi-user, multi-application instance nowadays the arguments for physical placement stop being "place this here and there" to become more of an "average the I/O needed to get this". IOW, what SAME is supposed to give us in the first place.

> Again, it would be an easyish thing to test. Rebuild such an index and see
> what effect it has on response times. My predication is that within the

Oh as far as response times go, there is nothing that will improve them more dramatically than more efficient SQL and db design, faster comms and faster CPU, in that order. I/O on average is not a major factor at all. Except of course in the odd extreme or boundary situations.

> Hope it makes sense.

Sure does. Thanks a lot.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam