当对表做delete操作的时候, 数据块是不会被释放的。 如果遇到查询效率很慢,排除索引和执行计划的因素外, 发现有大量物理读,但是返回记录行却很少。 这种情况就要考虑 有HWM问题。 需要考虑重建表或者使用SHRINK,MOVE 去降低 HWM。
Shrink table 测试
1. 创建含有数据的测试表
2. 查看表的段块使用情况---参考 show_space procedure
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................44
Last Used Ext BlockId...................696704
Last Used Block.........................128
HWM AT BlockID..........................171
HWM AT BlockS...........................256
Thesegment is analyzed
0% -- 25% free spaceblocks..............0
0% -- 25% free spacebytes...............0
25% -- 50% free spaceblocks.............0
25% -- 50% free spacebytes..............0
50% -- 75% free spaceblocks.............0
50% -- 75% free spacebytes..............0
75% -- 100% free spaceblocks............0
75% -- 100% free spacebytes.............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................256
Total bytes.............................2097152
3. 删除表中的一些数据
4. 分析表
5.查看表的段块使用情况
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................44
Last Used Ext BlockId...................696704
Last Used Block.........................128
HWM AT BlockID..........................171
HWM AT BlockS...........................256
Thesegment is analyzed
0% -- 25% free spaceblocks..............1
0% -- 25% free spacebytes...............8192
25% -- 50% free spaceblocks.............1
25% -- 50% free spacebytes..............8192
50% -- 75% free spaceblocks.............0
50% -- 75% free spacebytes..............0
75% -- 100% free spaceblocks............23
75% -- 100% free spacebytes.............188416
Unused Blocks...........................46
Unused Bytes............................376832
Total Blocks............................