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