Re: index rebuilding...

"Don Burleson" <don_at_burleson.cc> wrote in message news:998d28f7.0302011414.1f9b5c40_at_posting.google.com...
> Hi Richard,
>
> At last, a polite response!
>
> Is it possible that the index algorithms have changed since I did that
> article in 1995? Maybe I am obsolete!

You can say that again.

>
> My understanding of Oracle indexing was based on "borrowed" 7.3.3
> source code (donated by a disgruntled ex-Oracle employee), and the
> article that the arrogant, insulting, and self-aggrandizing Howard
> calls "rubbish" was published in back in 1995, when 7.3.4 was just
> released.

You posted in February 2003 that I knew nothing about it, that I was suffering from "delusions of knowledge", that "one of us doesn't know what they are talking about", and (here's the classic) "You really should take the time to check your facts before you
embarrass yourself".

Only now do we discover that your information is 8 years out of date (though a modicum of testing in that time would have put you straight).

You think it's OK to recommend 8-year-old information that is technically inaccurate, do you?

It always amuses me to see people whingeing about how "arrogant" and "insulting" I am when I point out they're wrong.

> Each data block within the index contains "nodes" in the index tree,
> with the bottom nodes (leaf blocks), containing pairs of symbolic keys
> and ROWID values. As an Oracle tree grows (via inserting rows into
> the table), Oracle fills the block, and when the block is full, it
> splits, creating new index nodes (data blocks) to manage the symbolic
> keys within the index. Hence, an Oracle index block may contain
> pointers to other index nodes or ROWID/Symbolic-key pairs.
>
> The number of entries within each index data block is a function of
> two values:
>
> 1 - The length of the symbolic key
> 2 - The blocksize for the index tablespace
>
> Because the blocksize affects the number of keys within each index
> block, it follows that the blocksize will have an effect on the
> structure of the index tree. All else being equal, large 32k
> blocksizes will have more keys, resulting in a flatter index than the
> same index created in a 2k tablespace.

In plain English, big blocks store more stuff than small blocks. Absolutely true. That nugget of information, at any rate, will still be true in 8 years' time.

>
> According to an article by Christopher Foot
> (www.dbazine.com/foot3.html):
>
> "A bigger block size means more space for key storage in the branch
> nodes of B-tree indexes, which reduces index height and improves the
> performance of indexed queries."
>
> (Oh dear, Mr. Foot used the "node" word. I'll bet Howard will be
> really pissed . . . .)

Not at all. He used the term correctly. An index is comprised of a root node, branch nodes and leaf nodes, within which are stored leaf entries. Mr Foot is referring to branch nodes perfectly properly: Unlike you, he hasn't referred to the entries themselves as nodes, but to the blocks within which they are stored.

> In any case, there appears to be evidence that block size affects the
> tree structure,

That much we've known for years.

>which supports the argument that data blocks (yes
> "nodes"),

So, you finally acknowledge the true terminology. Progress, I suppose. Now, if you could just also acknowledge that your 8-year-old article is not a technically-accurate description of index behaviour in 8.0, 8i, 9iR1 and 9iR2, we might be on to something.

>affect the structure of the tree.

HJR Received on Sat Feb 01 2003 - 16:47:11 CST