Re: Index management

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:4094002d$0$25328$cc9e4d1f_at_news-text.dial.pipex.com...

> It seems to me that your scenario rather assumes that nothing else of
> interest is happening on this device - as soon as you allow other segments
> to be placed on this device, or other processes to be interested in this
> device then any head movement during the scan will likely be lost amongst
> head movement due to the multi-user nature of the system. You could also ask

Well, pushing that concept to the extreme means also that we should never bother with clustering anything because multi-user access will destroy any benefits that may be gained. Which is probably why SAME came about?

Average the I/O wait across all tablespaces and forget about any space allocation optimisation other than the basics of blocking and reducing excessive recursive SQL? Maybe that is the way we should face this?

I'm all for simplification and if it means all I have to look at is the system I/O distribution counters and balancing I/O across disk subsystems, I love it! I'd rather control I/O distribution through the OS itself alone rather than have to learn two or three layers of optimization in order to tune I/O. Or alternatively, "trust it all to ASM and think of England"? ;)

> Interestingly though the conventional wisdom among sql-server folks (and I
> remain to be convinced of it but smart folks do argue it) is that one should
> do exactly what you describe above as a routine maintenance procedure, in
> fact on that system since most tables will in fact have a clustered index
> upon them you will likely be reorganising tables, probably weekly if not
> daily, exactly to avoid this 'fragmentation' type of issue. dbcc showcontig
> is the command that is what you are looking for in that environment. I'm not
> aware of an equivalent in the Oracle world.

Well, their rows are logically clustered around a given value of a non-unique key. This has been one of their "workhorses" for performance for well over a decade, when it first appeared in Sybase. I'm not sure it means anything in a NTFS environment where physical allocation is all over the place unless one takes precautions? Or with any modern SAN?

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