Re: Index management

Nuno,

Your description is correct - if you do a treedump (and have a version that doesn't dump entire leaf blocks) you can see the effect very clearly.

Taking you example and pushing it, you might see that in a clean growth of an index in an initially clean tablespace, with a monotonic key, that the index blocks when read in "physical" order go:

root,
100 x leaf blocks
2 x branch
100 x leaf blocks
branch
100 x leaf blocks
branch
100 x leaf blocks
branch
100 x leaf blocks
branch
100 x leaf blocks
branch
100 x leaf blocks
branch
A few more leaf blocks

If you now insert a row that has to go into leaf block 50, then that the entries from that block are shared between leaf block 50 and a new block stuck up at the HWM,
which might be block 750 in my sketch above.

So a range scan might now go:

    block 49, 50, 750, 51, 52

BUT - having worked out that a particular phenomenon can occur, you then have to
ask about the circumstances, and frequency, before you can comment on the impact on
performance.

Because of that one unlucky split, I have to do one extra physical I/O if I scan across two or three index leaf blocks - but if I'm doing that, I'm probably collecting lots of table rows anyway and if my table rows
are allowed to arrive out of sequence order, how many different table blocks will I have to visit to use all the rowids I get from the index ? Even if the table is sorted perfectly in order for the index, table row lengths tend to be much larger than index row length - so I could still have to read many more table blocks than index blocks. Will the one extra read matter enough to make it worth rebuilding the index.

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

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

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


> "Richard Foote" <richard.foote_at_tbigpond.nospam.com> wrote in message

> news:yUIkc.6619$TT.2282_at_news-server.bigpond.net.au...

>

>

> > many have got it so wrong. So I focused on those "experts" that seem to

> > publish a lot of "stuff" ...

>

>

> you mean the quantity of books published is not in direct

> proportion to expertise?  Now now, next you're gonna tell us that

> BCHR is not the most performance gauge...

>

>

>

> > No, it's definitely by volume. I showed this in a thread here a while

back


> > where after a leaf block split, the number of index entries in each

block


> > varied but the amount of used space sat at the 50% mark in each block.

>

>

> Great prezzie, Richard. Excellent info.  I do particularly like the point

> you raise about clustering the table data, not the index!  I've been

advising


> people to load their data in physical sequence of range scan keys for

years


> and have had tremendous performance improvements from just physically

> sorting data in tables where range scans are involved.  Now I know

precisely


> the reason why.

>

> After reading it, I've got a question for you if I may:

>

> Let's assume a situation where we have a root block, three branch blocks

> and a bucketload of leaf blocks, like in your slide #22.

> Assume as well that this index was created on a sequence-generated

> column (regular but not necessarily consecutive increase). As such in the

> pictorial representation the index leaf blocks would be organised left to

> right in increase value of the key.

>

> Now let's look at the physical (in the disk) distribution of this thing.

> Presumably the root block and the first branch and a few leaf blocks

> would be on consecutive blocks on the disk partition - assuming a raw

> disk, an "intelligent" file system might do other things...

> Followed by another branch block and more leaf blocks,then another branch

> block and more leaf blocks.  Correct assumption?

>

> If so, then let's assume that later interspersed insertions of new keys

> cause the third block from the left to split.  Like you say in your

slides,


> no problemo:  the new block is allocated from index freelist, the initial

> block gets "emptied" 50% into the new one and we do not get an increase in

> index level.  Fine and dandy.

>

> 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?

>

> Thanks in advance for any feedback.

> -- 

> Cheers

> Nuno Souto

> wizofoz2k_at_yahoo.com.au.nospam

>