Re: Index management

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:40936644$0$12740$afc38c87_at_news.optusnet.com.au...

> But now consider the physical distribution. Isn't that new block coming
> from the freelist? Which might be pointing to a first free block on "the
> other side of the disk", so to speak. This block will now LOGICALLY be
> between the original third and fourth leaf blocks, but physically very
> remote from these two original blocks.
>
> You know where I'm getting at now, don't you? If someone is now doing an
> index range scan - which for the sake of example will involve the third,
> new fourth and old fourth leaf blocks using the block-to-block link
> pointers, wouldn't it involve a heap of I/O wait while the arm got
> repositioned to get the new fourth block from "the other side of the
disk"?
>
> Wouldn't that be a case for an index rebuild, based purely on physical
> distribution rather than hazy "broken b-tree" concepts? What would be
> a way of finding out if this was indeed the case, apart from the obvious
> system I/O wait queue increasing in this device?

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 questions about the efficiency of a query that is doing a range scan over 3 or more leaf blocks - though naturally you'd need to see the query.

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.

Cheers

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com