Re: index rebuilding...

Incidentally, Don: I noticed you cut everything off before we got to your pearls of wisdom about bitmap indexes. So that I can be enlightened:

Pray tell me: where did the magic number 25 come from?

And why was there not the slightest mention of the inadvisability of bitmap indexes in a DML-heavy environment??

And why did you say that index rebuilds were "easy", when in fact they require exclusive table locks (even with the 'online' option in 8i and higher) and a great deal of performance-degrading I/O?

Did you just run out of space in your article, or what?

And since you deem my examples demonstrating deleted index entry re-use to be too simplistic and not-real-world, can we see your real-world tests demonstrating that deleted leaf rows *won't* be re-used????

Oh: and one more thing. The re-use of deleted leaf rows I demonstrated did not require the new entries to be 'adjacent' to the old ones, as you claimed. The first entry made into a leaf node that contains deleted entries causes *all* deleted entries for that node to be cleaned out, regardless of the specific value. An entry of 'BOLTON' would have cleared out all my 'A%' deleted entries, because all of them were in the same block.

HJR "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. 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?
>
> 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. I cannot
> comprehend that you think that Oracle will re-level a whole index tree
> in real-time. Think about it!
>
> > 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.
>
> > 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.
>
> > 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.
>
> > 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.
Received on Sat Feb 01 2003 - 07:47:03 CST