| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How do we know that an index need to be rebuilt.
The problem that rebuilding indexes is attempting to resolve is sparseness.
Lots of folks think that indexes "become unbalanced" over time and that is
the reason for rebuild, but Oracle B*Tree indexes are always balanced.
Sparseness is the symptom, however, not the cause. The problem with sparsely-populated index structures is that it takes more I/O (both logical and ultimately physical) to process all four operations performed on indexes: unique scan, range scan, full scan, and fast full scan.
What are the causes of sparseness? As Mladen indicated, deletions are one; index entries are not removed -- they are simply made unused and available for reuse.
The other cause of sparseness is monotonically-ascending data values (i.e. values generated from a sequence or timestamp, etc). Oracle B*Tree indexes are optimized for randomly-generated data values and when a block in an index fills, it splits rather than overflows. This split activity will cause the index to gradually be comprised of half-filled blocks. If the data is not monotonically-ascending, then backfill will occur and the half-full blocks in the index will ultimately fill. If the data is monotonically-ascending, then the blocks will stay half-full forever.
For the sparseness caused by deletion, rebuilds are often the only answer, unless the application can be "persuaded" to reuse data values in the indexed columns. :-)
For the sparseness caused by monotonically-ascending data values, either rebuilds can be used periodically to improve the situation or REVERSE-key indexes can be used to solve the problem permanently. However, be aware that using REVERSE-key indexes comes with restrictions.
on 9/22/03 1:04 AM, Mladen Gogala at mgogala_at_adelphia.net wrote:
> There is a Metalink note 77574.1 entitled "Guidelines on When to Rebuild a B- > Tree Index" which explains how to use "validate structure" and index_stats > table. It is far too scientific to ever put into the script. Explanation > for the rule of thumb is simple: if number of index blocks is big that means > that the number of deleted entries is likely to be big, so let's rebuild it. > As I said, rebuilding indexes is not often necessary and is usually done for > all the wrong reasons. Quality of the rule of thumb, therefore, doesn't really > matter. > > On 2003.09.22 03:19, Prem Khanna J wrote:
>>> Attachments are stripped from the list messages.
>>> On 2003.09.22 02:24, Prem Khanna J wrote: >>>> Hi Raju, >>>> Check the doc. i have attached.
> > -- > Mladen Gogala > Oracle DBA
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: tim_at_sagelogix.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Sep 22 2003 - 13:14:41 CDT
 |
 |