Re: Rebuild Indexes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 19 Feb 2004 09:42:43 -0000
Message-ID: <001301c3f6cc$b94c9130$6702a8c0@Primary>

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

Original Message ----- From: "John Kanagaraj" <john.kanagaraj_at_hds.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, February 18, 2004 4:59 PM Subject: RE: Rebuild Indexes

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

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----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
-----------------------------------------------------------------

Received on Thu Feb 19 2004 - 03:42:43 CST