RE: Coalescing Indxes on a regular basis
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <fmhabash@xxxxxxxxx>, "'Mercadante, Thomas F \(LABOR\)'" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- Date: Tue, 10 Apr 2007 16:04:14 -0400
Periodic examination of the indexes will not actually inform you of the
situation #1. You would have to have knowledge outside the database to make
that prediction. Knowledge of a shift in the operational state of a
particular index makes it a candidate for consideration. Whether actual
space reclaiming and possibly better performance in specific situations is
worth the work is always a legitimate question.
Possibly you could concoct an hueristic that if some table got a lot smaller
and didn't get big again for some period of time, then you would guess that
you should rebuild the indexes simply to reclaim the space. But more likely
you would know that something permanent had changed about the table because
a business plan or a change in applications or functionality was brought to
your attention.
Likewise it is unlikely I'd put something in place to constantly monitor the
blevel of all the indexes in a database, but I would examine ones known to
have a cyclical grow and shrink to see whether they can be sustained at one
depth less by periodic mainenance. Knowledge of the processing schedule
gives you a clue it is worth looking at, but you actually have to execute
experiments to determine whether there is a potential benefit to the cost.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of fmhabash@xxxxxxxxx
Sent: Tuesday, April 10, 2007 2:30 PM
To: Mercadante, Thomas F (LABOR)
Cc: oracle-l
Subject: Re: Coalescing Indxes on a regular basis
Thanks all. I'm well aware of the discussion on this matter. I do not
REBUILD myself on a any given schedule. I only do when...
1- Tables witness massive deletions after which they will have no
significant DML work.
<snip>
To be more PRECISE what is REGULAR or SCHEDULED about this is not
necessarily the actual coalesce but rather the index checks that take
place and If an index is caught in such a persistent state, then it can
be coalesced automatically.
<snip>
Why wouldn't you agree with this?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
