Re: index rebuilding...

> "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 Fri Jan 31 2003 - 22:45:15 CST