RE: Coalescing Indxes on a regular basis
- From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
- To: mwf@xxxxxxxx, fmhabash@xxxxxxxxx, "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- Date: Tue, 10 Apr 2007 15:19:53 -0500
If you turn on monitoring, which I think is available in 9i, and I know
is available in 10g, you will have a pretty good idea of the number of
deletes done on a table.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham
Sent: Tuesday, April 10, 2007 3:04 PM
To: fmhabash@xxxxxxxxx; 'Mercadante, Thomas F (LABOR)'
Cc: 'oracle-l'
Subject: RE: Coalescing Indxes on a regular basis
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
------------------------------------------------------------------------------
NOTICE: This electronic mail message and any attached files are confidential.
The information is exclusively for the use of the individual or entity intended
as the recipient. If you are not the intended recipient, any use, copying,
printing, reviewing, retention, disclosure, distribution or forwarding of the
message or any attached file is not authorized and is strictly prohibited. If
you have received this electronic mail message in error, please advise the
sender by reply electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your computer
system. Thank you.
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
