Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index Rebuilds
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