Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: index rebuilding...

Re: index rebuilding...

From: Sybrand Bakker <gooiditweg_at_nospam.demon.nl>
Date: Sun, 02 Feb 2003 00:14:51 +0100
Message-ID: <a1ko3v8s4p8kog7gqb7gnino9vneicfnu1@4ax.com>

Comments embedded

On 1 Feb 2003 14:14:32 -0800, don_at_burleson.cc (Don Burleson) wrote:

>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!

I don't think they have changed.

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

So your article may well not turn up as the second reference in Google and too many posters here don't search Google before posting.

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

I don't think this has ever been true. An index leaf block will always contain multiple leaf rows. You seem to imply each data block has only one index node, whatever that may be.

 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.

If this is a truly B*tree algorithm, it will split the index bucket, turning it into a non-leaf bucket, creating two new buckets, and balance the tree. An index block is either contains leaf rows or branch rows, by virtue of the (generally available) algorithms, it can *NEVER* contain a MIXTURE of leaf and non-leaf rows. If you look at the index_stats results after an ANALYZE INDEX that is EXACTLY what you are looking at.

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

AND the room for locking info
AND the freelist to mention only a few things.

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

I would consider this a myth. The only thing that would improve performance is a decreased depth of the index. Having observed depth behavior at various block sizes, I can safely say the depth of an index will almost always be 3, or 4, and a depth of 2 implies a very small table.

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

Which evidence? There isn't any, at least not in your response. The only thing which does hold true is that size of the block header is more or less fixed, and the net available storage per block is bigger. My experience however is that this doesn't result in indexes with a smaller depth.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Feb 01 2003 - 17:14:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US