Re: index rebuilding...

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sat, 1 Feb 2003 16:07:49 +1100
Message-ID: <duI_9.37922$jM5.96561@newsfeeds.bigpond.com>

"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 Received on Fri Jan 31 2003 - 23:07:49 CST