Richard's point was that regular rebuilding of all indexes was a total waste of
time as ALMOST ALL rebuilds were unnecessary. One of the boundary cases where
rebuilds MIGHT be appropriate was following a very large delete.
The OP has said that every month a process deletes everything older than 3
months - which means deleting 25% of the data. This is an ideal candidate for
identifying
a) Which indexes could be made unusable before the delete and then rebuilt
b) Which indexes should be rebuilt after the delete
c) Which indexes could, or should, be coalesced after the detel
d) Which indexes need no action at all.
I haven't been following the thread closely, so I don't know which version of
the database is in use, but there have been various bugs relating to index
block reuse (on inserts) after very large deletes - for which there's at least
one clue in the statistic called something like "abort on recursive index block
reclaim" - and one such bug could be "fixed" which goes away after a rebuild.
A key question, for analysis, is whether the deadlock is TX/4 (index based) or
TX/6 (data based).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Lothar Flatz [l.flatz@xxxxxxxxxx]
Sent: 17 October 2016 21:40
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Massive MERGE statement causing massive locks
Last time I looked it was Richard Foote. And yes, rebuilding indexes is
a myth .
https://richardfoote.wordpress.com/2007/12/11/index-internals-rebuilding-the-truth/
https://richardfoote.wordpress.com/category/index-rebuild/
In the case below we have no evidence. We don't know what was wrong
before or might be better now. I prefer to work with facts rather than
guess.
regards
Lothar
--
//www.freelists.org/webpage/oracle-l
