Re: Rebuild Indexes
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Feb 2004 09:42:43 -0000
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@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Naveen,
>Nahata (IE10)
>Sent: Tuesday, February 17, 2004 11:24 PM
>To: oracle-l@xxxxxxxxxxxxx
>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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
