Differences and Similarities Between Index Coalesce and Shrink Space
来源:互联网 发布:免费横道图绘制软件 编辑:程序博客网 时间:2024/06/13 22:34
trackback
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.
But that’s a discussion for another day …
- Differences and Similarities Between Index Coalesce and Shrink Space
- Similarities and Differences between Java and C++
- Differences Between Unix and Windows
- Differences between Hibernate and NHibernate
- Differences between C++ and Java
- Differences Between innodb_data_file_path and innodb_file_per_table
- Differences between WM_NOTIFY and WM_COMMAND
- Differences between HashMap and Hashtable
- differences between ATM and Ethernet
- Differences between Datasource and XADatasource
- Differences Between Heuristics and Metaheuristics
- Differences Between PyQt4 and PyQt5
- Differences between hashmap and hashtable
- differences between Proxy and vpn
- Differences between UDID and UUID
- Differences between ArrayList and Vector
- Differences between Octave and MATLAB
- Differences Between FTP and TFTP
- NYOJ123 士兵杀敌(四)(树状数组,插线问点)
- 获取屏幕旋转事件
- 数据存储之SQLite与LitePal<一>
- 4724: [POI2017]Podzielno
- 一个简单的爬虫开发
- Differences and Similarities Between Index Coalesce and Shrink Space
- linux中使用apt-get安装的文件位置
- mybatis动态执行自定义sql
- Android流畅性优化必备基础知识点---线程调度方式
- hdu 2018 母牛的故事
- HDU 4507 吉哥系列故事――恨7不成妻 (平方拆解 + *数位DP 总结)
- centos6.5 解决python2.6.6交互模式下方形键和退格键乱码的情况
- react native listview 实现下拉刷新(RefreshControl)
- 单链表总结