Oracle Alter table move 和 shrink的区别。

move 和 shrink都会移动表的高水位，但不同的是move不会释放已经申请的空间，move的时候会锁表，而且move会后索引会失效，shrink后会释放申请的空间，而且不会造成索引失效。此外碎片的整理也不一致。

    使用shrink的时候要注意，表要开启行迁移enable row movement，且表段所在表空间的段空间管理(segment space management)必须为auto，所以system表空间上面的无法shrink，

一. 首先我们来看一下碎片的整理情况

1. 建立测试表

PgSQL

SQL> create table test as select object_id,object_name from dba_objects where rownum <=5000; Table created. SQL> select count(*) from test; COUNT(*) ---------- 5000 SQL> exec show_space('TEST'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 26 Total Blocks............................ 32 Total Bytes............................. 262,144 Total MBytes............................ 0 Unused Blocks........................... 2 Unused Bytes............................ 16,384 Last Used Ext BlockId................... 240 Last Used Block......................... 6 PL/SQL procedure successfully completed.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> create table test as select object_id,object_name from dba_objects where rownum <=5000; Table created. SQL> select count(*) from test;   COUNT(*) ---------- 5000 SQL> exec show_space('TEST'); Unformatted Blocks .....................               0 FS1 Blocks (0-25) ......................               0 FS2 Blocks (25-50) .....................               0 FS3 Blocks (50-75) .....................               0 FS4 Blocks (75-100).....................               0 Full Blocks ............................              26 Total Blocks............................              32 Total Bytes.............................         262,144 Total MBytes............................               0 Unused Blocks...........................               2 Unused Bytes............................          16,384 Last Used Ext BlockId...................             240 Last Used Block.........................               6 PL/SQL procedure successfully completed.

2. 删除数据，制造碎片

3. 发现表上面的碎片已经产生了

4. 把表move，整理碎片

5.可以看到碎片整理结果，效果很好

6. 下面我们来看shrink的结果，首先创建测试表

7.制造碎片

8. 查看已经存在碎片

9. 使用shrink整理碎片

10. 查看整理后的碎片，发现还有一些碎片，整理效果不是很好。

11.然后在对其进行move，发现没有碎片，整理效果很好

上述结论，虽然shrink和move都是通过物理调整rowid来整理碎片的，但shrink space整理不彻底。只是尽可能的合并。

二：下面我们来看一下move和shrink高水位的问题。

1. 创建测试表

2. 查看分配的空间

3. 忘了分析表，这里可以看到分配了3个extents，24个blocks

4. 已经使用20个blocks，还剩下4个blocks

5. 下面删除一部分数据，再查看使用情况，可以看使用还是20个blocks

6. 实际只使用了12个blocks

7. 下面对表进行move，降低高水位HWM，这里不要忘了analyze表哦。可以看到使用的blocks数已经下降，高水位已经回收

move操作后，数据的rowid发生了改变，而index是通过rowid来fetch数据行的，所以，table上的index是必须要rebuild

8. 下面看一下shrink对hwm的回收

9. delete之后，高水位还是在block 15上

10.我们队表进行shrink后，可以看到分配的空间小了一半

PgSQL

SQL> alter table test enable row movement; Table altered. SQL> alter table test shrink space; Table altered. SQL> select segment_name , extents,blocks from user_segments where segment_name ='TEST'; SEGMENT_NAME EXTENTS BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- TEST 1 8 alter table <table_name> shrink space [ <null> | compact | cascade ];

1 2 3 4 5 6 7 8 9

SQL> alter table test enable row movement; Table altered. SQL> alter table test shrink space; Table altered. SQL> select segment_name , extents,blocks from user_segments where segment_name ='TEST'; SEGMENT_NAME                                                                                                                        EXTENTS     BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- TEST                                                                                                                                      1          8 alter table <table_name> shrink space [ <null> | compact | cascade ];

compact:这个参数当系统的负载比较大时可以用，不降低HWM。如果系统负载较低时，直接用alter table table_name shrink space就一步到位了

cascade：这个参数是在shrink table的时候自动级联索引，相当于rebulid index。

shrink必须开启行迁移功能。
alter table table_name enable row movement ;

保持HWM，相当于把块中数据打结实了
alter table table_name shrink space compact;

回缩表与降低HWM
alter table table_name shrink space;

回缩表与相关索引，降低HWM
alter table table_name shrink space cascade;

回缩表与相关所以，报错HWM

alter table table_name shrink space compact cascade;

回缩索引与降低HWM
alter index index_name shrink space

虽然在10g中可以用shrink ，但也有些限制：

1). 对cluster，cluster table，或具有Long，lob类型列的对象 不起作用。
2). 不支持具有function-based indexes 或 bitmap join indexes的表
3). 不支持mapping 表或index-organized表。
4). 不支持compressed 表

下面简单看下move后索引失效：

然后对表进行move，索引已经失效了

下面对索引进行rebuild后，索引生效

PgSQL

SQL> alter index test_idx rebuild; Index altered. SQL> select index_name,status from user_indexes; INDEX_NAME STATUS -------------------------------------------------------------------------------------------------------------------------------- -------- TEST_IDX VALID

1 2 3 4 5 6

SQL> alter index test_idx rebuild; Index altered. SQL> select index_name,status from user_indexes; INDEX_NAME                                                                                                                       STATUS -------------------------------------------------------------------------------------------------------------------------------- -------- TEST_IDX                                                                                                                         VALID

测试一下shrink之后，索引的状态，可以看到shrink后，索引依然生效

最后编辑：2014-01-25

作者：Jerry

一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL

站内专栏 站点 QQ交谈