Re: index rebuilding...

"Richard Foote" <richard.foote_at_bigpond.com> wrote:

> Now let's see how Oracle has "documented" the additional leaf node in the
> branch node:

...
> row#0[8017] dba: 37750253=0x24005ed *** But we now have a new leaf
> pointer inserted between the previous two. This points to the new leaf
> node that has been added as a result of the leaf node split ***
> col 0; len 12; (12): 44 69 61 6d 6f 6e 64 20 44 6f 67 73 *** values
> greater or equal to DIAMOND DOGS go here ***
> col 1; len 6; (6): 02 40 05 e8 00 88

Richards wonderfull explanation prompted me to ponder a question. "Diamond Dogs" was embedded in the branch block in it's entirety. I thought I had read somewhere that only the minimum distinguishing length was stuck in branch blocks, so I was wondering if that was true. Surely all twelve characters aren't needed to distinguish "Diamond Dogs" from something else. But then I realized that the index is very non-unique. And indeed, if you look at the last row of the next block dump below, and first row of the one after that, you see "Diamond Dogs" appears on both sides of the split.

So it appears that when Oracle finds an equal key in a branch node, it has to follow both branches (or more likely, follow one branch and then use the linked list to get where the other branch was pointing to.)

To attempt to distinguish Diamond Dogs from Diamond Dogs, of course it takes all 12 letters. But if the split had happened to fall between David Bowie and Diamond Dogs, would the branch block have recorded "Diamond Dogs", or would it just use "Di" as being sufficiently distinctive?

...
> Let's see how our poor "splitted" leaf node looks like:
<skip to last row>
> row#173[7992] flag: -----, lock: 0
> col 0; len 12; (12): 44 69 61 6d 6f 6e 64 20 44 6f 67 73
> col 1; len 6; (6): 02 40 05 e8 00 74

Diamond Dogs here, too.

> And finally, an extract of the new leaf node dump and how it "slots" into
> the index structure:

...
> row#0[4566] flag: -----, lock: 0
> col 0; len 12; (12): 44 69 61 6d 6f 6e 64 20 44 6f 67 73 *** and as
> recorded in the branch node, entires in this now second leaf node start
> with DIAMOND DOGS values ***

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB