Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SAP Reorgs

Re: SAP Reorgs

From: Tim Gorman <>
Date: Fri, 18 Jun 2004 12:11:58 -0600
Message-ID: <>

Iım kind of confused about why there is any discussion about this topic... Reorgs are bad, bad, bad. You need downtime to accomplish them, else a non-trivial amount of preparation and cleverness (or an expensive 3rd-party product) to avoid that downtime. As someone remarked earlier in this thread, we wouldnıt be having a discussion on reorgs if they were painless. But they are painful (by varying degrees), so it is prudent to quantify the benefits before expending the effort and incurring the risk, right? They certainly do not fall into the realm of ³best practice², I think we can agree?

So, while quantifying the benefits, Iıve found that using reorgs generally to enhance performance and save storage is much like using liposuction as a general method of weight loss. I can see (maybe!) doing liposuction once in a lifetime for a small number of problems, but one would expect the patient to learn the necessary lessons and prevent a repeat. I canıt imagine a reputable doctor recommending yearly liposuctions.

Of course, if regular liposuction surgery can be justified, then Iım sure similar justifications can be posed for regular reorgs... :-)

on 6/15/04 4:03 PM, at wrote:


>> > To my opinion reorganizing is OK when , as a result of it, a table or index
>> > occupies less data blocks.
>> > This will, in general, not only cause less LIO for this segment.

> Rebuilding a B*Tree index to conserve space can have detrimental effects on
> performance.
> If the index sees a lot of insert activity, your newly rebuilt index will
> undergo block splits, and
> soon be back to the size it was previously.
> FFS and Range Scans may benefit from a rebuild, but it would probably be best
> to
> quantify the benefit.
> This goes for tables too, dependent on whether or not a table sees many FTS,
> and the
> access patterns. If straight OLTP, rebuilding to save space may not buy much
> performance.
> It may take less space in the block buffers, but then again, previously cool
> blocks may
> become hot.
> There are no silver bullets.
> See Richard Foote's paper on index internals, it is very informative.
> I'm sure he will correct me if I have mis-spoken on any of this. ;)
> Jared

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Jun 18 2004 - 14:16:07 CDT

Original text of this message