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
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?
--
//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:
