Idx coalesce / shrink usage
DanielDJan 9 2009 — edited Jan 9 2009
I decided to test the coalesce/shrink operations on a 62 GB index. (10.2.0.4)
I'd like to speed up things using nologging parallel if possible.
-- This step completed in under 45 minutes
ALTER INDEX UNIUS.IDX_SBA_IN_MAIN_001 COALESCE NOLOGGING PARALLEL 18;
-- This step completed in 3,5 hours, now I am puzzled. The blocks
-- are already packed shouldn't this just release space in like notime ?
ALTER INDEX UNIUS.IDX_SBA_IN_MAIN_001 SHRINK SPACE; -- released 12GB
What's interesting is that actual rebuild of the whole index takes roughly
45 minutes or so. So isn't coalesce/shrink completely useless on big segments
with the only single advantage of not locking the segments?