RE: Coalescing Indxes on a regular basis

From: Kerber, Andrew W. <Andrew.Kerber_at_umb.com>
Date: Tue, 10 Apr 2007 14:02:08 -0500
Message-ID: <D40740337A3B524FA81DB598D2D7EBB306F0EF6C@x6009a.umb.corp.umb.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of fmhabash_at_gmail.com 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?

--

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 Received on Tue Apr 10 2007 - 14:02:08 CDT