As already discussed, ALTER INDEX COALESCE in 10g onwards works in a very similar manner to ALTER INDEX SHRINK SPACE.
However, there are a number of key differences.
The first thing to point out is that each command has a slightly different purpose.
Coalesce is designed specifically to reduce fragmentation within an index but not to deallocate any freed up blocks which are placed on the freelist and recycled by subsequent block splits.
Shrink is designed specifically to reduce the overall size of an index segment, resetting the High Water Mark (HWM) and releasing any excess storage as necessary.
The key difference being that Shrink must reorganise the index leaf blocks in such a way that all the freed up, now empty blocks are all grouped together at “one end” of the index segment. All these blocks can then be deallocated and removed from the index segment. This means that specific leaf block entries must be removed from these specific blocks, in order to free up the leaf blocks in this manner.
Although Coalesce in 10g performs the operation in a similar manner to that of the Shrink Space, it can be more “lazy” in how it deals with the subsequent empty blocks and places then on the segment freelist as necessary.
COALESCE and SHRINK SPACE COMPACT are logically equivalent commands. Both options will “defragment” an index by “merging” index entries where possible thus reducing the number of blocks within the logical index structure. Both will result in the same number of leaf blocks within the index and both will result in the index height not being changed.
However, there are two key differences.
1) The SHRINK SPACE COMPACT option has the disadvantage of being more expensive to process as it has to concern itself with ensuring all necessary blocks can be emptied from the physical “end” of the index segment to be subsequently deallocated. This will result in more undo and redo being generated during the defragmentation of the index than would have been generated by the same corresponding COALESCE command.
2) The SHRINK SPACE COMPACT option has the advantage of being able to immediately deallocate the empty blocks, thereby reducing the actual size of the index segment by issuing a subsequent SHRINK SPACE option (although of course this can be performed in the one step by issuing SHRINK SPACE in the first place). However, the COALESCE option will not be able to just deallocate the free space. A subsequent Index SHRINK SPACE command on a previously coalesced index will require additional undo and redo than that of a previously “Shrunk” index as the necessary empty blocks are removed from the freelist and redistributed to allow for the de-allocation of blocks and the resetting of the High Water Mark of the index segment.
Note also that the Shrink option can only be used in Automatic Segment Space Management (ASSM) tablespaces.
Use Coalesce when the intent is to just defragment an index, knowing that the freed leaf blocks will be recycled by subsequent block splits, as it uses less resources than an equivalent Index Shrink Space.
Use Shrink Space when the intent is to reduce the actual storage allocated to an index, for example in the scenario where a table has permanently reduced its size and the index is unlikely to reuse the freed storage.
This demo highlights the Differences (and similarities) between an Index Coalesce and an Index Shrink Space.
Note however, that an index REBUILD might actually use substantially less resources than either a Coalesce or a Shrink Space and might reduce the height of an index as well.
参考至:http://richardfoote.wordpress.com/2008/02/06/differences-and-similarities-between-index-coalesce-and-shrink-space/
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
Differences between C++ and C# 可以看看。
IFRS and US GAAP similarities and differences 2018 - PwC
Evolutionary Programming and Evolution Strategies Similarities and Differences
Cultural Differences in Diets Between China and the West.zip
This invaluable resource will help practitioners and students sort out differences and similarities between popular myofascial styles. Early chapters offer a solid review of anatomy and physiology as ...
Differences between EEWB and BDT
however, there are some significant differences between the capabilities of Silverlight and WPF, as well as important differences in the programming features of the two technologies. There are ...
What are the differences between least-squares and Kalman filtering
(正文)Similarities and differences from the perspective of value.zip
Talk Show Differences Between China and America from the Perspective of the Form and Express.zip
The Differences Between Chinese and American Cultures from the Perspective of Politeness Utterances.zip
Differences Between S32K11x and S32K142,11x系列与K142的不同
state 50 differences between marketing and sales
It will guide you through installing the Android SDK for Intel Architecture, help you understand the differences and similarities between processor architectures available in Android devices, teach ...
The information about differences and similarities between these groups would certainly be interesting for a professional working in South London; however, it is overly detailed for an American ...
There is a detailed segment comparing solid-state drives (SSDs) to conventional hard disk drives (HDDs), explaining all the differences and similarities between them in an easy to understand fashion....
siggraph2015上的一个分享,介绍新一代图形API 的相似和异同
The information about differences and similarities between these groups would certainly be interesting for a professional working in South London; however, it is overly detailed for an American ...
飞思卡尔为处理器IMX51与IMX53的区别
Contributions from the foremost international researchers and practitioners in the GP arena examine the similarities and differences between theoretical and empirical results on real-world problems....