Previously, I discussed how an ALTER INDEX … COALESCE is going to be less expensive in terms of using resources than an equivalent ALTER INDEX … SHRINK SPACE COMPACT (or ALTER INDEX … SHRINK SPACE) as the Coalesce doesn’t have to concern itself with ensuring all leaf blocks at the physical end of the index segment have all been moved to allow for the storage to be de-allocated from the index segment. If you just want to de-fragment an index and not necessarily reduce the overall space allocated to the segment, use Coalesce rather than the Shrink options as it’s cheaper.
But what about an ALTER INDEX … REBUILD, when, if ever, should it be used ?
Well the answer is as with most things Oracle, it depends.
We have a table and the application deletes historical data but in a manner in which leaf blocks are not being entirely emptied. Basically, older stuff is removed, but it’s only removed in a random manner, from approximately the “earlier” 10% of the table. The index is sequenced which means only those leaf blocks in the “left-most” 10% of the index structure are impacted but all this deleted space is “deadwood” as new index entries are only being inserted into the “right-most” part of the index.
Note that basically 90% of the index is fine and very well utilised, it’s only 10% of the index that’s problematic. Of the problem 10% of leaf blocks, there’s plenty of free or deleted space, with many leaf blocks almost but not quite empty.
Coalesce (and indeed Shrink) will basically run through these 10% of fragmented leaf blocks and will merge the index row entries into as few leaf blocks as possible. With the 90% of blocks that are fine, Coalesce will basically read and then ignore them from any processing as there’s nothing that can be done for them.
Rebuild on the other hand will take an entirely different approach. It will (generally) read the entire existing index structure and will build a brand new, bright and shining index segment. As part of this process, it will rebuild the entire index, it has no choice (assuming the index isn’t partitioned, but that’s another story) and will rebuild the 90% of the index that was actually perfect to begin with. Rebuilding 90% of something that doesn’t need rebuilding doesn’t sound particularly efficient and indeed it isn’t. As a result, the index rebuild will use substantially more resources and generate substantially more redo than an equivalent Coalesce (or Shrink Space).
We have an application that deletes data and it deletes data throughout the entire index structure. The deletes are significant with a substantial proportion of the overall rows having been deleted. Additionally, the table is not going to be repopulated with anything like the same volume of data or it won’t be repopulated for a substantial period of time. As such, all this deleted index space is “deadwood” as it’s not going to be used any time soon, if at all.
Now typically in this sort of scenario, it’s of course the table as much as the associated indexes that needs to be rebuilt. That’s a key point. However, maybe Full Table Scans are not an issue for this table so the wasted space in the table is not of urgent concern. Maybe the table in not in an ASSM tablespace or in a database that supports a Table Shrink command and maybe moving the table is not an immediate option due to availability concerns. For whatever reason (or lack of reason), the index needs to be de-fragmented.
Note it’s the entire index that’s problematic here and there could be portions of the index that have very few remaining index entries.
Now poor Coalesce (and indeed Shrink) has a bit of an issue here. They both merge index entries from two blocks into the one block where it can. However, if leaf blocks are really empty, these merged index entries may in turn be merged and moved again with index entries from yet another leaf block. And maybe yet again with another leaf block. And again and again … So a specific index entry may actually be moved into several different leaf blocks during the entire process. Each of these moves requires resources and generates redo and takes time.
Now the rebuild has an entirely different approach. As mentioned, it will basically (generally) read the entire exisiting index structure and will build a brand new one, but importantly as it does so will only have to locate a specific index entry once and once only. Also, as it’s the entire index structure that’s problematic, there’s no issue with fixing the entire index, as it’s all “broken”.
As a result of only having to deal with an existing index entry the once vs. the Coalesce which may relocate a specific index entry many times, the index rebuild is going to be substantially more efficient and potentially use significantly less resources and generate less redo.
This demo of the Differences between a Coalesce, Shrink Space and Rebuild shows when one out performs the other.
Basically, Coalesce is particularly efficient and uses less resources when the percentage of the overall index structure that’s problematic and fragmented is relatively small (less than approximately 20-25% of leaf blocks). Rebuild is particularly efficient when the percentage of the overall index structure that’s problematic and fragmented is relatively large and the average degree of fragmentation within an index leaf block is relatively high. Note Pre 10g, an index needed to have at least 50% free space less pctfree in neighbouring leaf blocks for a Coalesce to be effective.
Now Rebuild (and Rebuild Online) potentially have locking implications that need to be considered although as we’ll see later, 11g has addressed some of these issues …
Differences and Similarities Between Index Coalesce and Shrink Space February 6, 2008Posted by Richard Foote in Index Coalesce, Index Shrink, Oracle General, Oracle Indexes, Performance Tuning.
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 …
ALTER INDEX COALESCE: 10g Improvements (Jump They Say) February 5, 2008Posted by Richard Foote in Index Coalesce, Oracle General, Oracle Indexes, Performance Tuning.
I thought it might be worth mentioning some interesting changes in the manner in which the ALTER INDEX … COALESCE command works since Oracle 10g.
Basically the purpose of the COALESCE option is to reduce free space within the Leaf Blocks of an index. This is achieved by effectively performing a Full Index Scan of the leaf blocks, comparing the free space available in neighbouring blocks. In 9i, the basic method was to logically start with the left most leaf block and see if it could be coalesced or merged with the 2nd left most block. This required the sum of used space within these 2 blocks to be less than 100% of a block less the PCTFREE value. If so, the contents were merged with the contents of one block placed in the other and with the now empty leaf block removed from the index structure and placed on the index freelist.
It then looked at the 2nd leaf block (which might now be the first block if previously coalesced) and 3rd leaf blocks to see if these could be coalesced. If so they were merged and the empty block placed on the freelist.
And so on and so on until all leaf blocks had been traversed and all possible leaf blocks coalesced.
Note branch blocks are not directly merged during this process, except to be updated with modified pointer information if a leaf block coalesce had taken place. However, if enough leaf blocks are removed such that the branch block contains no more pointers to leaf blocks (or other intermediate branch blocks), it’s also removed from the index structure. However, there must always be at least one branch block from each level remaining hence the height of an index always remains the same during a coalesce operation.
Note if no leaf block had 50% or more free space, nothing would be coalesced as no two consecutive leaf blocks would have sufficient free space in which to be coalesced.
In 10g, the Coalesce operation has been modified somewhat.
An index no longer requires the sum of used space plus PCTFREE in adjacent blocks to be less than 100% of a block be effectively coalesced. For example, the free space in a block can be 25% in one leaf block and just 25% in the adjacent block (hence the combined used space alone being 150% of a block) and 10g can effectively coalesce these leaf blocks together.
This demo show how Coalesce differs between a 9i (126.96.36.199) and a 10g (10.2.0.3) database.
10g introduced the concept of being able to SHRINK an index and the Coalesce option can be viewed as now being very similar to an index Shrink command. Similar but not quite the same.
I’ll cover the similarities and differences between a Coalesce and a Shrink in the next day or two …
Index Internals – Rebuilding The Truth December 11, 2007Posted by Richard Foote in Index Coalesce, Index Height, Index Internals, Index Rebuild, Index Shrink, Index statistics, Oracle Indexes, Oracle Myths, Oracle Opinion, Richard's Musings.
The issue of when to rebuild indexes crops up again and again and again. Unfortunately, so do the same incorrect, myth-filled uninspired responses which considering how important and “key” (no pun intended) indexes are to database design and performance generally, is very puzzling indeed.
In the coming days I’ll post why I believe these index related myths simply refuse to go away …
This presentation was originally written several years ago but is still as relevant today as it’s always been.
Recently updated version: Index Internals – Rebuilding The Truth