considerations for rebuilding and coalescing

Giganews Newsgroups
Subject: considerations for rebuilding and coalescing
Posted by:  Ben (balv…
Date: 18 Aug 2006

running DMTs Ent Ed. on AIX5L with compatible parameter set to

Ok, we have recently started archiving old data out of our largest
tables that are connected to our ERP. Our largest table had over 50
million rows that took up appx 40G of space, it now contains and
hopefully will continue to contain around 30 million rows. So we
decreased it by 40% The ERP system requires a bunch of redundant
indexes and the indexes on this table alone made up 98G of space. This
figure should be reduced by 40% now as well. I understand that I will
have to rebuild those indexes before we can reclaim that space that
will not be used again.

First question, how much space do I need to account for to make sure
that a rebuild will not fail? If the index is 50M but it's 40% empty
how much freespace does the tablespace need to have? enough for two 50M
indexes or 1 50M and 1 30M? Are there any precautions that need to be
taken in consideration to the TEMP table space? I know that I've seen
posts on rebuilds failing because of running out of room in TEMP tbspc,
I believe that was explained as the segments being created as temp
segments in the permanent tbspc while the rebuild is running and not
actually TEMP tbspc.

Next question, these indexes that occupy 98G of space but have 40G of
Freelist blocks are still growing. The all grabbed a new extent just
this week. Is it because a block on the freelist isn't necessarily 100%
free and the index needs a block at the front of the index? If that is
the case, will coalescing take care of that issue for the time being,
until I can rebuild and reclaim the unused space? Or does coalescing
only combine adjacent leaf blocks?

I've also got a huge problem with freelist blocks not being used. I
think our 250G database is about 30% to 40% empty, or should I say 30%
to 40% of our datablocks are on the freelist but they are not being
utilized. New blocks are being grabbed. I had a post a while back and
didn't come to a complete conclusion as to why but I think it has a lot
to do with all of our objects only having 1 freelist.

I appreciate all the good advice that you have all given me in the
past, please keep it coming.