Re: Index management

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:c704ie$l53$1_at_sparta.btinternet.com...

> So a range scan might now go:
> block 49, 50, 750, 51, 52

I was trying to figure out if this could occur. It appears to me that assuming:
1- a very volatile table where its index also gets "pushed around", 2- a separate partition for the index,

there might be a case for needing to re-build an index. Nothing to do with a problem with the way Oracle stores the indexes or the nature of their implementation of B-trees, but just on pure physical optimisation grounds.

Obviously only to be done if needed. The problem of course would be to prove the need. I'm just wondering if there is any way it could be done by querying meta-data on the database rather than rely on potentially ad-hoc deltas of system I/O wait counters, always notoriously unreliable.

I suppose one could build a query to analyse distribution of blocks by rowid on the sequence of the range scans, but I'm having difficulty figuring out how to produce the results in a way that would be helpful or even make sense.

> If you are suspicious of a particular index, and
> want to examine the details, you really need to
> investigate the tree dump, or even the full block
> dump. There's a site I've just found with an
> interesting couple of articles about internal structures
> that also has a product that uses an external procedure
> to turn the internal structure of Oracle blocks into
> a view - the articles are at
> www.tlingua.com

Thanks. INTERESTING stuff, that BCB.SQL script! It might solve a problem I had before, must spend some time digesting it.

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