'收缩空间紧凑'和'合并'之间有什么区别？ [英] What are the differences between 'shrink space compact' and 'coalesce'?

问题描述

oracle文档说在更改期间index子句缩小空间紧凑和 coalesce 非常相似，可以互相替换，但Tom 发现行为。

The oracle documentation says that during altering an index clauses shrink space compact and coalesce are quite similar and could be replaced by each other, but Tom found some differences in the behavior.

由于合并版在Oracle数据库的标准版中不可用，我认为使用它有一些好处。

Since coalesce is not available in standart edition of Oracle Database, I suppose there're some benefits in using it.

那么，有什么区别？我可以在动态变化的索引上执行缩小空间紧凑吗？

So, what are the differences? Can I perform shrink space compact on a dynamically changing index?

推荐答案

以上答案是错误的。基本上有4个选项。

The above answer is false. There are basically 4 options.

1 - ALTER INDEX COALESCE

1 - ALTER INDEX COALESCE

2 - ALTER INDEX SHRINK SPACE

2 - ALTER INDEX SHRINK SPACE

3 - ALTER INDEX SHRINK SPACE COMPACT

3 - ALTER INDEX SHRINK SPACE COMPACT

4 - ALTER INDEX REBUILD

4 - ALTER INDEX REBUILD

选项1和3不会释放块。他们只是释放现有街区的空间。 Coalesce做得更糟糕，将会有更多的块，只有25-50％的可用空间，而收缩空间紧凑，将有更多的块，75-100％的可用空间。但是，块的总数保持不变。例如，一个200块具有合并的索引，并且在随机删除1/5行之后，将有~1 / 5的索引块具有25-50％的可用空间，而其余的则保持满。

Options 1 and 3 do NOT free up blocks. They just free up space in existing blocks. Coalesce does a little bit worse job, there will be more blocks with only 25-50% free space, while with shrink space compact, there will be more blocks with 75-100% free space. The total number of blocks, however, stay the same. For example, an index with 200 blocks with coalesce, and after deleting 1/5 of the rows randomly, will have ~1/5 of the index blocks have 25-50% free space while the rest remain full.

另一方面，缩小空间和重建会释放块，并将它们合并到现有块中，从而减少块的总数。我认为唯一的区别是速度。当您从大表中仅删除5％时，没有理由重建整个索引，而且速度非常慢。但是，缩小空间在这里可能要快一些，因为它不会重建整个索引，只需重新组织块。

On the other hand, shrink space and rebuild do free up the blocks, and merge them into existing ones, thus reducing the total number of blocks. I think the only difference is speed. When you delete only 5% from a large table, there's no reason to rebuild the entire index, and it will be very slow. However, shrink space might be a little bit faster here, because it does not rebuild the entire index, just reorganizes the blocks.

显然，最快的选择是合并或缩小空间紧凑的选项。

Obviously the fastest choice would be coalescing or shrinking space with compact option.

这篇关于'收缩空间紧凑'和'合并'之间有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！