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 Rebuilds

Re: Index Rebuilds

From: Stan Towianski <stantow_at_ibm.net>
Date: 1997/07/14
Message-ID: <33cab34c.0@news1.ibm.net>#1/1

Hi,

This is a message I found looking for this myself recently. I also found that with oracle 7.3 at least the fastest way to rebuild an index is with:
alter index xxx rebuild;

From: radranly_at_us.oracle.com
Newsgroups: oracle.support.supportlink.oracle7 Subject: Re: index rebuild
Date: 4 Jun 1997 05:55:22 -0700
Message-ID: <180520612120174.ORC_at_support.us.oracle.com> References: <169438574424387.ORC>

RDBMS Version (if applicable): oracle7
Error Number (if applicable): oracle7
Product and Version: oracle7
Operating System: oracle7

First, after you analyze an index, check the values in Index_stats, a good rule of thumb for rebuilding an index is when PCT_USED < 50%
(indicating that on average less than half an index block is
being used by keys), and BLK_GETS_PER_ACCESS > 5 (meaning a height from root to leaf is greater than 5). NOTE: this is a rule of thumb.

There is no concept of update in an index, when a table row is updated, the old index
key is deleted and the new key inserted (at the correct location in the B*tree). As a result, PCTFREE is not relevant for indexes after creation. At creation time, when all pairs in the table are being sorted and injected into the B*tree PCTFREE can be used to limit the usable space in each data block -- forcing splits in the index blocks and leaving sparse blocks that can accomodate rows/keys that are inserted by the user later.

The idea is to do as much splitting as possible during initial creation and avoid having to pay the penalty later during insertion into the table. This is what a high PCTFREE setting on an index can buy you. However, if your inserted keys are monotonically increasing (say a date/time field) a PCTFREE=0 is best -- only the rightmost index leaf will be inserted into, so there's no point leaving room in the others at creation time.

There is no PCTUSED for indexes.

As for index balancing, you are right, what we mean by the B-trees stay balanced, is that the number of blocks traversed (I/Os needed) to get to any leaf block from the root block is the same. However, it is possible for the B-tree to be 'skewed' ie. more number of leaf blocks on one side of the root block compared to the other side. This happens if the values being inserted into the index are monotonically increasing or decreasing (you mentioned sysdate). The way to fix the skew in an index is to drop and recreate the index. The pct_used < 50% from above should give you some indication of a skewed index.

Reem

(posted newsgroup reply and sent email)

stantow_at_ibm.net Received on Mon Jul 14 1997 - 00:00:00 CDT

Original text of this message

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