RE: Coalescing Indxes on a regular basis

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Tuesday, April 10, 2007 3:04 PM
To: fmhabash_at_gmail.com; '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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]

On Behalf Of fmhabash_at_gmail.com
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?

--
http://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.

==============================================================================

--
http://www.freelists.org/webpage/oracle-l