Re: Coalescing Indxes on a regular basis

From: <fmhabash_at_gmail.com>
Date: Tue, 10 Apr 2007 14:30:20 -0400
Message-ID: <461BD7BC.4040008@gmail.com>

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.

2- Tables get moved.
3- Following an import.
4- Tables goes from read/write to read only (after a migration or system 

load).
5- The index browning scenario even though I have never seen it myself.

My inquiry was about scenarios 1 and 5. In an enterprise environment where you have 100's of databases, we as DBAs do not get notified of changes of this nature. So if a table does witness this massive deletion with no subsequent significant DML or if there is an index on a some sequence generated value with sporadic deletions that leaves the leaves unused but keeps the nodes. These leaves are never back on the free list. The question becomes how can we detect these 2 scenarios on our own and take appropriate action.

The only indication I have in mind here is a PERSISTANT deleted leaf rows that is may be > 35%. If you do have an index that persists in this status, why wouldn't you coalesce it (not rebuild)?

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.

Why wouldn't you agree with this?

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

Received on Tue Apr 10 2007 - 13:30:20 CDT