This was first published on https://blog.dbi-services.com/what-about-alter-index-shrink-space (2014-10-20)
Republishing here for new followers. The content is related to the the versions available at the publication date
I have recently published a script to check index fragmentation. But then, do you COALESCE or REBUILD? Well, there also is another option: ALTER INDEX SHRINK SPACE. Let’s compare all those index defragmentation operations.
I have an index created when the table had 1 million rows. Then, I deleted 90% of the rows. Here is the index state from:
and here are the results:
:UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS ---------- ---------- ---------- ---------- ---------- ---------- ------- 0 0 1 0 0 2230 2304 HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED ------- ------- ---------- -------- -------- -------- ----------- ---------- -------- 3 2304 100404 2226 2225 5 404 1622013 10 N to N rows/block bytes/block %free space blocks free ---------- -- ---------- ---------- ----------- ----------- ------- ----- 10 -> 250280 45 714 91 557 oooo 250730 -> 500370 45 714 91 557 oooo 500820 -> 750010 45 714 91 556 oooo 750460 -> 999660 45 714 91 556 oooo
I have 2226 leaf blocks, the index height is 3 with 5 branch blocks. The leaves are only 91. However dbms_space shows only full blocks: the deleted entries are still there, just marked as deleted, and the blocks are still seen as full.
Now let’s COALESCE:
SQL> alter index DEMO_N coalesce;And before checking the same values about the index space I measure the amount of work that has been done by the operation (from v$mystat):
NAME VALUE ---------------------------------------------------------------- ---------- redo entries 22067 db block gets 32818 session logical reads 32886 db block changes 40601 undo change vector size 35199264 redo size 47878800 :UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS ---------- ---------- ---------- ---------- ---------- ---------- ------- 0 0 2004 0 0 227 2304 HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED ------- ------- ---------- -------- -------- -------- ----------- ---------- -------- 3 2304 100000 223 222 5 0 1591530 88 N to N rows/block bytes/block %free space blocks free ---------- -- ---------- ---------- ----------- ----------- ------- ----- 10 -> 248690 452 7170 11 56 253200 -> 500800 450 7158 11 56 505310 -> 752020 449 7132 11 56 756530 -> 998730 443 7038 12 55
COALESCE is an online operation that defragments the leaf blocks. We have now only 223 leaf blocks that are 90% full (because my pctfree is the default 10%). But the index stil has the same height and still has 2300 blocks. Where are the reclaimed 2000 blocks? They are available if the index need a new block (for a block split). They are seen as FS2 (at least 25 to 50% free space) by dbms_space because they still contain the deleted rows, but they are fully reclaimable anyway.
Back with the same table, and doing a SHRINK SPACE COMPACT instead of COALESCE:
NAME VALUE ---------------------------------------------------------------- ---------- redo entries 28794 db block gets 40940 session logical reads 41527 db block changes 49387 undo change vector size 36990460 redo size 51848880 :UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS ---------- ---------- ---------- ---------- ---------- ---------- ------- 0 0 1 0 2003 227 2304 HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED ------- ------- ---------- -------- -------- -------- ----------- ---------- -------- 3 2304 100000 223 222 5 0 1591530 88 N to N rows/block bytes/block %free space blocks free ---------- -- ---------- ---------- ----------- ----------- ------- ----- 10 -> 248690 452 7170 11 56 253200 -> 500800 450 7158 11 56 505310 -> 752020 449 7132 11 56 756530 -> 998730 443 7038 12 55
So what is the difference? Slightly more work (about 20% more logical reads and block changes) for the same result. Except that now the reclaimed blocks are in FS4 (75 to 100% free space).
What if we use SHRINK SPACE instead of SHRINK SPACE COMPACT?
NAME VALUE ---------------------------------------------------------------- ---------- redo entries 29352 db block gets 45496 session logical reads 46190 db block changes 50032 undo change vector size 36981524 redo size 51901500 :UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS ---------- ---------- ---------- ---------- ---------- ---------- ------- 0 0 1 0 0 227 240 HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED ------- ------- ---------- -------- -------- -------- ----------- ---------- -------- 3 240 100000 223 222 5 0 1591530 88 N to N rows/block bytes/block %free space blocks free ---------- -- ---------- ---------- ----------- ----------- ------- ----- 10 -> 248690 452 7170 11 56 253200 -> 500800 450 7158 11 56 505310 -> 752020 449 7132 11 56 756530 -> 998730 443 7038 12 55
With tables, the shrink space lowers the high water mark. Here it is the same idea: in addition to the shrink space the reclaimed blocks are no more allocated to the index, so it can be used for other segments in the tablespace. We see that from dbms_space: the index is now 240 blocks only.
The previous requires a lock only for a short duration (according that we did the shrink space compact before). The rebuild needs a Share lock on the table during the whole operation, blocking concurrent DML.
NAME VALUE ---------------------------------------------------------------- ---------- db block gets 953 redo entries 1832 db block changes 1906 session logical reads 4019 undo change vector size 9152 redo size 173732 :UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS ---------- ---------- ---------- ---------- ---------- ---------- ------- 0 0 1 0 0 222 256 HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED ------- ------- ---------- -------- -------- -------- ----------- ---------- -------- 2 256 100000 222 221 1 0 1591520 90 N to N rows/block bytes/block %free space blocks free ---------- -- ---------- ---------- ----------- ----------- ------- ----- 10 -> 248690 452 7170 11 56 253200 -> 501250 451 7170 11 56 505760 -> 749300 451 7170 11 55 753810 -> 997350 448 7117 11 55
The result is the same as the previous operation (SHRINK SPACE) except that the index height has decreased. A rebuild is the right operation if the index blevel has become too high. And we did that offline but with much less work. Minimal undo and redo. And small blocks to read (when the index is still usable the rebuild can use the current index to rebuild the new segment).
Last operation, possible only in Enterprise Edition, is the rebuild online which doesn’t need to lock the table.
NAME VALUE ---------------------------------------------------------------- ---------- redo entries 660 db block changes 876 db block gets 1419 session logical reads 4989 undo change vector size 24932 redo size 114924 :UNF :FS1 :FS2 :FS3 :FS4 :FULL BLOCKS ---------- ---------- ---------- ---------- ---------- ---------- ------- 0 0 1 0 0 222 256 HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS USED_SPACE PCT_USED ------- ------- ---------- -------- -------- -------- ----------- ---------- -------- 2 256 100000 222 221 1 0 1591520 90 N to N rows/block bytes/block %free space blocks free ---------- -- ---------- ---------- ----------- ----------- ------- ----- 10 -> 248690 452 7170 11 56 253200 -> 501250 451 7170 11 56 505760 -> 749300 451 7170 11 55 753810 -> 997350 448 7117 11 55
Here we don’t see an overhead to do it online. This is because my table is small (my testcase has only one column which is the indexed one). On a real table you will probably see that the online rebuild takes longer than the offline one, because it cannot use the current index. But anyway, the fact that it is online means that the duration is not a big issue.
This is an example on a table that had a massive purge: all blocks were touched. In that case the REBUILD is the right solution. However if you are in Standard Edition and cannot do it online, then you will probably do a SHRINK SPACE COMPACT because it lets you do a SHRINK SPACE (need a quick locks but for a very short duration – to do on a period of low activity). COALESCE will make sense here only if you know you will insert back a lot of rows, so that you don’t need to deallocate the blocks from the index.
Now, what to do if the free space to reclaim is only on small part of the index blocks? As in the following case:
N to N rows/block bytes/block %free space blocks free ---------- -- ---------- ---------- ----------- ----------- ------- ----- 10 -> 50468 374 5974 26 112 o 50917 -> 100756 449 7179 11 112 101205 -> 151044 449 7179 11 112 151493 -> 201332 449 7179 11 112 201781 -> 251620 449 7179 11 112 252069 -> 301908 449 7179 11 112 302357 -> 351747 449 7179 11 111 352196 -> 401586 449 7179 11 111 402035 -> 451425 449 7179 11 111 451874 -> 501264 449 7179 11 111 501713 -> 551103 449 7179 11 111
In that case a COALESCE is probably the best because you keep the blocks allocated to the index for future growth. And the few blocks reclaimed will probably not change the index height anyway. However, if you did a small purge that concern only a small part of the index and want to reclaim the space for other segments, then the SHRINK SPACE is the right solution. But do you really need to reclaim space in that case?
Now you see the usage for my index fragmentation script: I don’t need only the average free space. I need to have a clear picture of the fragmentation in order to decide what to do and how.
There is something else important in the INDEX SHRINK vs. COALESCE when there are concurrent DML activity. COALESCE just skip the blocks where some index entries are locked. SHRINK will wait on them.