Re: SAP Reorgs

From: <Jared.Still_at_radisys.com>
Date: Tue, 15 Jun 2004 15:03:44 -0700
Message-ID: <OF34C40E0A.70714F23-ON88256EB4.0078A735-88256EB4.00792F60@radisys.com>

> 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: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jun 15 2004 - 17:00:44 CDT