Forum Stats

  • 3,855,345 Users
  • 2,264,499 Discussions
  • 7,905,975 Comments

Discussions

Idx coalesce / shrink usage

DanielD
DanielD Member Posts: 119 Blue Ribbon
edited Jan 9, 2009 4:49AM in General Database Discussions
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?
Tagged:

Answers

  • Daniel

    It's worth reading Richard Foote's blog, eg [Index Rebuild vs Coalesce vs Shrink|http://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/].

    Richard's blog is a great source of iinformation about indexes, with plenty of examples.

    Regards Nigel.
This discussion has been closed.