Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Rebuild Indexes

Re: Rebuild Indexes

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 20 Feb 2004 00:32:04 +1000
Message-ID: <06bf01c3f6f5$251bdfb0$0100000a@FOOTE>


The next biggest issue is probably the scenario when a table has undergone a significant *and* permanent reduction in size (or it's a significant time until the table is to be repopulated) *and* it impacts performance. However, if the indexes have been fragmented to the point of impacting performance, then it's almost certain the table itself is fragmented to the point where it should be rebuilt (thus implicitly causing the rebuilding of all the indexes anyways). All a bit of an index rebuild fizzle really ;)

The third biggest issue is a little more subtle and hence somewhat rarer (hence why it's third ;). If an index is significantly large enough *and* there are enough repeating values such that they span a significant number of nodes *and* it's a small enough proportion of the table for Oracle to still consider using the index *and* it impacts performance. The issue here being that each 50-50 block split will result in a 1/2 empty node that can not be filled or reused (unless it's subsequently completely deleted) as the left hand node will contain only the one index value but Oracle will only ever use the right most node that has an occurrence of the particular index value. Hard to "draw" it in an email, but assuming that each group of values represents a leaf node and 6 values could fit in one node, you'll have a pattern such as this:

aaa aaa aaa aaa aaabbb bbb bbb bbb bbb bbb bbb bbbccc ccc ccc ccc cccddd etc ...

The value "a" can only be inserted into the 5th node, the previous 4 nodes are *permanently* 1/2 full. When the 5th node splits, it will leave behind yet another 1/2 empty node containing just the value "a" that can't be filled.

Such indexes *could* benefit from an occasional rebuild and that a coalesce may not be sufficient in this case. Deletes can futher worsen the problem, but increase the potential benefit of a coalesce.

I'm currently part way through writing an article on "Index Internals", primarily for both Daniel Fink (RMOUG) and my local user group which (hopefully) many on the list may be interested in.

Cheers

Richard
----- Original Message -----
From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> To: <oracle-l_at_freelists.org>
Sent: Thursday, February 19, 2004 7:42 PM Subject: Re: Rebuild Indexes

John,

Yes, the "biggest issue" requires three simultaneous events:

    column is monotonic increasing (or monotonic     decreasing, though that's a rare beast) with time.     That includes indexing on date/time stamps.

    MOST, but not all data from the past is deleted,     so index leaf blocks can be left holding just one or     two entries.

    Queries have to scan for current data by starting     at the beginning of the index.

In this case, the scans will find that they are scanning through a large number of old, nearly empty, leaf blocks to find a few current rows.

For such indexes your remedy is good: a one-off rebuild when you realise what's been going on, followed by a regular coalesce to repack as many of the near-empty leafs as possible.

Even so, this may not make much different to performance as you still have to scan the index - and if the scan ALSO requires you to visit the table for every index entry, the cost of the redundant visits to the table is probably going to be serious.

Under Oracle 8 and 10, an index on a sequence-based (monotonic increasing) column would pack to 100% if the application was coded properly - many weren't, of course. But there is a bug in Oracle 9.2 (I haven't checked 9.0) which results in 50/50 block splits, and 50% packing on single row inserts.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearances:
 March 2004 Hotsos Symposium - The Burden of Proof  March 2004 Charlotte NC OUG - CBO Tutorial  April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Naveen,

I believe the problem is not with a monotonically increasing index (which should NOT be rebuilt as it is of no use) - rather it is with a monotonically increasing index that is also being _deleted. This is typical of indexes on FIFO-type tables such as the FND_CONCURRENT_REQUESTS in Apps (any version). In the latter case, you should perform a 'one-time' index rebuild of such monotonically-increasing, followed by regular COALESCE of the indexes to 'reclaim' 'lost' space. Jonathan/Richard Foote might have something to add on this....

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com

>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Naveen,
>Nahata (IE10)
>Sent: Tuesday, February 17, 2004 11:24 PM
>To: oracle-l_at_freelists.org
>Subject: RE: Rebuild Indexes
>
>
>John,
>
>Would it be advisable to go for Index rebuilds in case of
>index based on
>monotonically increasing columns? As much as I understand (and
>I must admit,
>i don't know much about the internals of indexes), in case of
>such indexes
>the problem of 'leaf block split' should not happen.
>
>Regards
>Naveen



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Feb 19 2004 - 08:32:04 CST

Original text of this message

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