Re: index rebuilding...

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!

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.

At that time, (and I have the source code filed away somewhere) Oracle index nodes were organized by data blocks, where each data block was an index node. That is why you cannot specify PCTUSED for an index, because Oracle must govern the freelist re-link during splitting and spawning operations.

Please correct me if any of the following is incorrect:

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.

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 . . . .)

In any case, there appears to be evidence that block size affects the tree structure, which supports the argument that data blocks (yes "nodes"), affect the structure of the tree. Received on Sat Feb 01 2003 - 16:14:32 CST