Re: ALTER INDEX COALESCE

Roger,

The coalesce command combines leaf blocks within the same branch of the index tree. Unless you know how fragmented the index is, it could be difficult to predict how much redo is generated.

    Perhaps one way you could test this would be to coalesce some indexes on a test system and then use log miner to try to determine just what is being written to the redo logs. My guess is that since coalesce doesn't perform much work, it probably doesn't generate much redo.

   Note that several people on this list that are much smarter than me have written about the futility of rebuilding indexes unless some special events have occurred like lots of deletes. Their point is that (from my small understanding) a B+ tree index in a normal, operating system has a small amount of fragmentation in the indexes. Fortunately this fragmentation rarely gets out of hand. You can go to a lot of effort to rebuild those indexes but very quickly it will return to a small amount of fragmentation so your effort is largely wasted. My assumption is that coalescing indexes would fall under the same category.

Dennis Williams

--
http://www.freelists.org/webpage/oracle-l