Re: ALTER INDEX COALESCE
- From: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
- To: Roger.Xu@xxxxxxxxxxx
- Date: Wed, 2 Aug 2006 09:57:05 -0500
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
Other related posts:
