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:
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@(protected) >
To: <oracle-l@(protected) >
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
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@(protected) >
To: <oracle-l@(protected) >
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@(protected)
>[mailto:oracle-l-bounce@(protected)] On Behalf Of Naveen,
>Nahata (IE10)
>Sent: Tuesday, February 17, 2004 11:24 PM
>To: oracle-l@(protected)
>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@(protected)
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
