shrink机制初探

shrink是10g中引入的新特性，可以在线对表进行类似重组的操作，降低高水位，且对业务基本没有影响，shrink的分2个阶段进行

 （1）Segment data is compacted. Through a series of INSERT and DELETE statements (during which DML-compatible locks are held on individual rows or blocks of the table), the segment data is moved as far to the beginning of the segment as possible. Given that rowids change, you must enable row movement and also disable any triggers based on rowid for table segments you want to shrink.

    这个过程实际上是在segment内部进行一系列的insert和delete，把segment中后面的数据尽可能的移动到segment前面，使数据更加紧凑。

（2）High-water mark (HWM) is adjusted to an appropriate location (exclusive locks are held on the data at this point), and unused space is deallocated from the segment, so it is available for the tablespace to reallocate to other objects as needed.

  这个过程是将segment的高水位降低，释放存储空间。

下面通过试验来演示shrink的过程：

在表t1上有10行记录，分别分布在在4个block中

SQL> select a,dbms_rowid.rowid_block_number(rowid) from t1;

         A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
         7                                   28
         8                                   28
         9                                   28
        10                                   30
         1                                   31
         2                                   31
         3                                   31
         4                                   32
         5                                   32
         6                                   32

将block_id=31上的3条记录删除，

SQL> delete from t1 where a<=3;

3 rows deleted.

SQL> commit;

Commit complete.

SQL>  select a,dbms_rowid.rowid_block_number(rowid) from t1;

         A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
         7                                   28
         8                                   28
         9                                   28
        10                                  30
         4                                   32
         5                                   32
         6                                   32

7 rows selected.

执行表的shrink操作：

SQL> alter table t1 shrink space;
alter table t1 shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table t1 enable row movement;

Table altered.

SQL> alter table t1 shrink space;

Table altered.

SQL> select a,dbms_rowid.rowid_block_number(rowid) from t1;

         A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
         7                                   28
         8                                   28
         9                                   28
         4                                   29
         5                                   29
         6                                   29
        10                                  30

7 rows selected.

可以看出，原来位于block id为32上的三条记录4，5，6已经被move到了block id为29的块上，表的存储结构变得更加紧凑，高水位得以降低。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-600708/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-600708/