Oracle b-trees

Consensus in this news group seems to be that Oracle's flavor of b-tree is height-balanced with leaf nodes only found at the lowest level. Maybe I misread some messages, but it seems to me that some people are saying that indexes with b-tree height of 3 or 4 will give access to any leaf by reading 3 or 4 blocks.

Assuming that blocks are OS blocks or Oracle multi-blocks (multi-block read) at worst, it seems to me that, with only 3 or 4 levels, beyond a certain number of rows (and depending on skewness of the b-tree, perhaps)  you must read multiple blocks at at least one level in order to find a pointer to the correct range of items at the next level. Beyond a certain point, the number of items in a b-tree branch node must exceed the number of items that fit in an OS block. Now that I think of it, I suppose that by "block" they must mean "branch or leaf node".

Any thoughts on this subject?

And has anyone heard of a stagnant index bug where query by leading column of an index for only one particular leading value in a table (and no others) results in response times 5 or 6 times slower than a full table scan and IOs approximately equal to sum(blocks in index + blocks in table)? The value is always the highest value less than the low value from a recent massive delete.

Thanks.

Paul Kelley
pkelley_at_coat.com

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet