RE: Coalescing Indxes on a regular basis

  • From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
  • To: fmhabash@xxxxxxxxx, "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
  • Date: Tue, 10 Apr 2007 14:02:08 -0500

Empirical evidence has shown me that on tables exceeding 20 million
rows, even a 5% delete on the table can cause measurable degradation in
performance that can be fixed by rebuilding the indices.  I have not
tried coalescing the indices, so I don't know if the same effect can be
achieved by coalescing them.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of fmhabash@xxxxxxxxx
Sent: Tuesday, April 10, 2007 1: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.
2- Tables get moved.
3- Following an import.
4- Tables goes from read/write to read only (after a migration or system

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?


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.



Other related posts: