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 [EMAIL PROTECTED] 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: >> Read ur interesting reply for the query Mladen and learnt >> a new "rule of thumb" from u today. >> >> i sent the doc. to Raju ONLY adn NOT to the list. >> it's just the body of the mail u r seeing there. >> >> Regards, >> Jp. >> >> 22-09-2003 15:49:40, Mladen Gogala <[EMAIL PROTECTED]> 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. >> >> >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.net >> -- >> Author: Prem Khanna J >> INET: [EMAIL PROTECTED] >> >> 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: [EMAIL PROTECTED] (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). >> > > -- > Mladen Gogala > Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
