Oracle-L: RE: fragmentation

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 14 Aug 2003 12:34:23 -0800
Message-ID: <F001.005CAB82.20030814123423@fatcity.com>

Rachel - Right you are, as "Stop Defragmenting . . . " points out, there are several types of fragmentation.

Is it your table or your index that contains wasted space?
Are there multiple objects in each tablespace or just a single object?
Are you trying to keep the index available to users while you're rebuilding? I'm assuming this is the reason you are looking at rebuilding the index twice. Or is it because rebuilding an index probably won't cause a large sort? Overall it looks like a reasonable plan, though I haven't worked much with partitioned indexes myself.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, August 14, 2003 12:29 PM To: Multiple recipients of list ORACLE-L

I never thought I would care about fragmentation again, especially once I built all my databases using LMTs.

But I've actually found a reason to care, sort of. We have a data warehouse with lots of "wasted" space in it. By that I mean, many of the partitions (we partition by month) are way larger than is needed for the data contained within them. Once all the data for a month is loaded, that's it, it doesn't grow anymore.

The oversized tablespaces are those associated with the indexes. Part of the problem is that the hosting company we use has a threshhold of 80% and when a tablespace is 80% full, they automatically expand the datafiles. part of the problem is that for a time there were problems with the loads and we had to delete/re-insert/delete/re-insert data.

I want to shrink the datafiles, but they are "fragmented". yes, I know "disk is cheap", but having gone through a 3 month exercise in frustration trying to get the data center management to spend a few thousand dollars on more memory so that we could actually run reports, I'm not going there.

I was planning on doing the following, just wanted a sanity check from the list:

create a very large holding tablespace, to use as a rebuilding area

then, on a partition by partition basis:

rebuild the index partition into the holding tablespace (lots of indexes to rebuild in each partition)
rebuild the index partition back into the original tablespace (my hope is that this will effectively "compress" the index extents)
shrink the index partition datafiles

Does this make sense or am I overtired and not thinking? Is there a better/faster/EASIER way to do what I wanted to do?

Rachel

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------