Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rebuild indexes

RE: Rebuild indexes

From: Mark W. Farnham <>
Date: Tue, 24 May 2005 07:28:44 -0400
Message-ID: <>

I'd like to substitute "There is often no good reason for doing so" in place of your last sentence, while agreeing with the sentiment that just rebuilding indexes (or pretty much any other activity without a good reason) not only consumes otherwise possibly useful cycles but also may have deleterious side effects.

One big case is when your rate of inserts is small compared to the accumulated size. Thus the compactness gained on balance offsets any extra leaf splits driven by new inserts. A special case of this case is when no more inserts will be happening with respect to the particular index, and a large class of this case is local indexes on partitions where there is a time of creation component in the partition key. Notice, however, that these rebuilds are likely to be justified very infrequently. In the first case the proposition is that the continued rate of inserts is small compared to the current size, so future significant compaction is pretty far in the future, and in the special case, once the time boundary is crossed no more insertions will occur and the only rebuild would be to replace an index that becomes invalid for some reason.

Another case is when an index that normally is receiving monotonically increasing keys has had exceptions such that the "right most" block is no longer the main insert point. Then a rebuild will crunch the index down and the special case split of the "right most" block will be restored unless there are sufficient numbers of "high keys" to keep the main insert point "lefter?" in the tree. So there is a case where a periodic treadmill rebuild might actually be justified. (Without comment or evaluation of what design process might result in having such an index, which, after all is not something many operationaly DBAs control.) Even if there is some reason in design fallout for keys in a special range, if you know you're using Oracle, you should make them "low keys." Again, I am not trying to justify arriving at such a case, but if you find yourself in that box canyon please avoid creating one of the very few cases remaining in Oracle that may unavoidably keep the operational DBA on a treadmill.

There might be more cases and I am not trying to enumerate all possibilites.

Probably Jared was using "generally" in the colloquial sense of "most all the time" rather than the strict "all the time" sense as in the difference between Special Relativity and General Relativity. Regardless, his main point of potentially causing unexpected harm due to treadmill maintenance is spot on.



-----Original Message-----
[]On Behalf Of Jared Still Sent: Monday, May 23, 2005 11:27 AM
Subject: Re: Rebuild indexes

In addition to the excellent explanations you have already received, rebuilding
this index will cause a considerable amount of extra activity during subsequent
inserts while the index bulks itself back up to its former size. Why are you rebuilding it? There is generally no good reason for doing so.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

On 5/22/05, Peter Dixon <> wrote:

> I have an application where data is never deleted, I have just rebuilt an
> index which was previously 4gb in size and its now 3gb in size. Can
> anybody
> explain?
> --
-- --
Received on Tue May 24 2005 - 07:36:20 CDT

Original text of this message