Re: index rebuilding...

"Howard J. Rogers" wrote:

> "Don Burleson" <don_at_burleson.cc> wrote in message
> news:998d28f7.0301312045.7ca1d667_at_posting.google.com...
> > > "Indexes require rebuilding when deleted leaf nodes appear". I don't
> even
> > > know what a deleted leaf node is.
> >
> > Yes, that is very apparent.
>
> The point, which appears to have sailed straight over your head, is that
> they are called leaf ROWS, not leaf NODES. As the report in your article
> actually goes on to show.
>
> >Despite your delusions of knowledge about
> > a "pure" B*Tree, an Oracle index tress cannot be a pure b*tree because
> > it is impossible to index 100m rows in only four levels in a b*tree.
> > Did you flunk your basic data structures class?
>
> Er, where exactly did I say that such an index had to have a height of four?
> Nowhere, that's where. What I said, and which is true, is that if its 4 on
> one side, it is 4 on the other, not 3 and 4 as you wrote in the article.
>
> > Oracle maintains many sub-keys within each inode.
> > When the number of
> > entries exceeds the internal "index_block_contains" threshold
> > (normally 50, depending on the key length), the Oracle index splits
> > until it reaches the maximum supported level of its superior node. At
> > that point, the index "spawns" into another level.
>
> Big deal. We know this. What you've missed out that if it spawns a new
> level, there's a deal of re-organisation in the branch blocks (and the root
> block, if required) to maintain a consistent height across the entire index.
>
> > I cannot
> > comprehend that you think that Oracle will re-level a whole index tree
> > in real-time. Think about it!
>
> I have. It does. Don't believe me? Ask Jonathan. Or Tom Kyte. Or Steve
> Adams. Or, come to that, me.
>
> > > Oracle re-uses deleted leaf row space, all on its own.
> >
> > Yes, Einstein, in your simplistic example Oracle will re-use an
> > adjacent deleted node that rarely happens in the real-world. In most
> > cases, indexes with significant DML will have large amounts of dead
> > leaf nodes.
>
> Leaf rows you mean.
>
> And how you can say that beats me. Unless your index is on a monotonically
> incrementing sequence number, then you can't rule out the possibility of an
> insert or an update wanting to make use of deleted space. So where you have
> 'significant DML' is precisely where the deleted leaf rows will be re-used.
> Assuming that your DML is broadly-speaking 'randomized'.
>
> And why you persist in inventing your own terminology for what Oracle has
> already perfectly clearly named also beats me.
>
> But it doesn't surprise me.
>
> >
> > > Next. " Oracle index nodes are not physically deleted when table rows
> are
> > > deleted". I would have more confidence in the advice you offer if you
> could
> > > get the distinction between a node (ie, a block) and a row right.
> >
> > Yes, I agree, one of us does not know what they are talking about.
>
> Please do a describe on INDEX_STATS. Please report back what the column
> headings are. If there's one called 'LEAF_NODES', I'll eat copious
> quantities of Humble Pie. Oh, alright. Tell you what: I'll do it for you.
>
> HEIGHT
> BLOCKS
> NAME
> PARTITION_NAME
> LF_ROWS
> LF_BLKS
> LF_ROWS_LEN
> LF_BLK_LEN
> BR_ROWS
> BR_BLKS
> BR_ROWS_LEN
> BR_BLK_LEN
> DEL_LF_ROWS
> DEL_LF_ROWS_LEN
> DISTINCT_KEYS
> MOST_REPEATED_KEY
> BTREE_SPACE
> USED_SPACE
> PCT_USED
> ROWS_PER_KEY
> BLKS_GETS_PER_ACCESS
> PRE_ROWS
> PRE_ROWS_LEN
> OPT_CMPR_COUNT
> OPT_CMPR_PCTSAVE
>
> Gosh. Not a "LEAF_NODE" column in sight, though I see one called "LF_ROWS"
> and another called "DEL_LF_ROWS". Guess I have to go hungry then.
>
> If you're going to post to an *Oracle* newsgroup, citing an article
> addressing *Oracle* index issues, then it behoves you to stick to *Oracle*
> terminology. It also behoves you to get it right, but that's a tad harder to
> pull off, obviously.
>
> > > As for this: "Note that Oracle indexes will "spawn" to a fourth level
> only
> > > in areas of the index where a massive insert has occurred, such that 99%
> of
> > > the index has three levels, but the index is reported as having four
> levels.
> > > " ... well, all I can say is: this is complete baloney.
> >
> > You really should take the time to check your facts before you
> > embarrass yourself.
>
> Hi Kettle. You must be Pot.
>
> >
> > > Oracle's index structures are B*Trees, meaning that they are 'balanced'
> trees.
> >
> > No genius, look at the DESCTs. If you take the trouble to decompile
> > the code and look at a few block dumps, you can see exactly how it
> > works.
>
> Been there, done that. You're wrong.
>
> Your index article is about as good as some of the other performance tuning
> stuff you've published in the past. IE, not very.
>
> HJR
Thank you all for this spirited, if somewhat testosterone-laden, celebration of index balancing.

My students will no doubt continue to benefit from your knowledge and wisdom.

Now about that Macallan I had last evening.

Daniel Morgan Received on Sat Feb 01 2003 - 01:09:17 CST