Oracle Tips And Tricks Part #3

Tips 12: Rebuild indexes & gather statistics after table shrink.

When you finish a table segment shrink, it is a good practice rebuilding indexes of that table and also gather statistics of that table. These thinks help Oracle to create better execution plans for the SQL quarries related with the table.

Tips 11: Shrink table segment that experienced large delete

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space. With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline. The process of shrinking a segment :

1. Enable row movement : ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
2. Recover space and amend the high water mark (HWM): ALTER TABLE scott.emp SHRINK SPACE; OR
   
3. Recover space, but don't amend the high water mark (HWM) : ALTER TABLE scott.emp SHRINK SPACE COMPACT;
4. Recover space for the object and all dependant objects : ALTER TABLE scott.emp SHRINK SPACE CASCADE;

You can use 2 or 3 but 3, both have some advantage/ disadvantage. Follow 3 for best performance.

To learn segment shrink please go through the following two articles :

1. http://shaharear.blogspot.com/2008/05/online-segment-shrink.html
2. http://shaharear.blogspot.com/2008/06/segments-shrink.html