Databases Reference
In-Depth Information
During an index shrink operation, in the first phase the database scans the index segment from the
back to locate the position of the last row. Next, the database scans the index segment from the
beginning to locate the position of the first free slot in an index block. If the two positions are identical,
there's no need to do anything further. As long as the database finds that the two positions are different,
it continues to remove rows from the back of the index and insert them into the free blocks at the front of
the index segment. The
shrink space
command is inherently more expensive than a coalesce operation
because the command actually deallocates empty index blocks and places them on the free list. It must
empty all index blocks from the physical end of the index segment for this deallocation to occur and all
this requires more work on part of the database and the generation of more redo. Use the
shrink space
command only if you want to permanently reduce the size of the index segment, say because of a large
number of permanent deletions from a large table; you won't need that space ever, so you can shrink the
index segment. If your goal is merely to defragment an index, coalesce the index instead of shrinking it;
you'll have all the freed up index blocks for subsequent use by the index and the operation requires far
fewer resources.
Although both the
coalesce
and
shrink
commands achieve the same purpose of defragmenting an
index by rearranging existing index entries to reduce the number of blocks in an index structure, you use
the two commands for different purposes. Whether you must coalesce or shrink an index depends on
what is happening with the index. If you think the index is unlikely to grow much and has a lot of free
space, you may want to shrink the index to reclaim the free space. However, if you think the index will
probably need the free space in the future, you may want to just coalesce the index. Coalescing has an
advantage over shrinking an index because the database never locks the index during the index
coalescing operation (coalesce is
always
an online operation), while it does lock the table briefly during a
shrink operation to release the free space.
Large indexes sometimes get fragmented over time and you may have a valid reason to reduce the
fragmentation. Whether you employ the coalesce or rebuild operation depends on exactly how the index
is getting fragmented. If you have an index on a sequence, for example, and the rows are deleted from an
older part of the index, then you may have a case where the deleted space does become deadwood in the
sense that it can't be used by new index entries that are being inserted into the newer, rightmost part of
the index. In a case such as this, where the deletions are all occurring from a small portion of the index, a
rebuild is overkill in general; you're better off with a coalesce (or shrink operation). The coalesce
operation will just go through the small portion of the index that's fragmented and leave the rest of the
index alone; it takes far less time and resources to get the job down via coalescing the index in this case.
However, if there are numerous deletions through the index, and not from a specific part of the index
structure, you are better off rebuilding the index completely. Rebuilding is far more efficient because,
unlike a coalesce operation, it has to perform only a single scan of the index structure in order to create a
new structure. Since the deletions are large and are all over the index structure, coalescing an index is far
less efficient, as it has to keep moving the same index blocks through multiple leaf blocks to defragment
them. The choice between rebuilding and coalescing an index can also depend on whether you need to
avoid downtime. If you can't have downtime, then you've got to coalesce since, as mentioned earlier,
coalescing is always an online operation.
Moving Tables and Indexes
Whenever you move a table to a different tablespace (or perform any one of several table maintenance
procedures), any indexes that depend on the table are rendered unusable. Here is an example:
SQL> alter table test move tablespace dev_oim;
