considerations for rebuilding and coalescing

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

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.

Ben Received on Fri Aug 18 2006 - 14:57:07 CDT