[Oracle] Shrink space & Table move比较

本实验是对两个一样的table，测试shrink space 和 table move释放了多少空间，以及对index status的影响.

1.新建了两个一样的table tt5,tt7
SQL> select (select count(*) from tt5) tt5_count,(select count(*) from tt7)  tt7_count from dual;

 TT5_COUNT  TT7_COUNT
---------- ----------
   2619392    2619392

SQL> select segment_name,bytes from user_segments where segment_name in('TT5','TT7');

SEGMENT_NAME              BYTES
-------------------- ----------
TT5                   301989888
TT7                   301989888   

2.delete 一定量的数据
SQL> select (select count(*) from tt5 where object_id>25000) delcount,(select count(*) from tt7 where object_id>25000) delcount from dual;

  DELCOUNT   DELCOUNT
---------- ----------
   1393408    1393408
   
SQL> delete from tt5 where object_id>25000;

1393408 rows deleted.

SQL> delete from tt7 where object_id>25000;

1393408 rows deleted.

SQL> commit;

Commit complete.

SQL> select segment_name,bytes from user_segments where segment_name in('TT5','TT7');

SEGMENT_NAME              BYTES
-------------------- ----------
TT5                   301989888
TT7                   301989888

SQL> select (select count(*) from tt5) tt5_count,(select count(*) from tt7)  tt7_count from dual;

 TT5_COUNT  TT7_COUNT
---------- ----------
   1225984    1225984

tt5,tt7 的segment大小没有变化

4.对tt5,tt7分别进行table move,shrink space
SQL> alter table tt5 move;

Table altered.

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

SQL> alter table tt7 enable row movement;

Table altered.

SQL> alter table tt7 shrink space;

Table altered.

SQL> select segment_name,bytes from user_segments where segment_name in('TT5','TT7');

SEGMENT_NAME              BYTES
-------------------- ----------
TT5                   142606336
TT7                   131530752      

SQL> select index_name,status from user_indexes where table_name in('TT5','TT7');

INDEX_NAME                        STATUS
-------------------------------- ----------
TT5_INDEX                         UNUSABLE
TT7_INDEX                         VALID

上面可以看出shrink space比table move更加缩小空间(差距很小)，table move会将index status置为unusable


5.tt7 disable row movement
SQL> alter table tt7 disable row movement;

Table altered.

6.对tt7进行move后，tt5,tt7两个table的大小一样了。为啥shrink space&table move不一样呢，奇怪...
SQL> alter table tt7 move;

Table altered.

SQL> select segment_name,bytes from user_segments where segment_name in('TT5','TT7');

SEGMENT_NAME              BYTES
-------------------- ----------
TT5                   142606336
TT7                   142606336

7.重新rebuild index
SQL> select index_name,status from user_indexes where table_name in('TT5','TT7');

INDEX_NAME               STATUS
----------------------- ----------------
TT5_INDEX                UNUSABLE
TT7_INDEX                UNUSABLE

SQL> alter index tt5_index rebuild;

Index altered.

SQL> alter index tt7_index rebuild;

Index altered.

SQL> select index_name,status from user_indexes where table_name in('TT5','TT7');

INDEX_NAME             STATUS
--------------------- ----------------
TT5_INDEX              VALID
TT7_INDEX              VALID

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

转载于:http://blog.itpub.net/24237320/viewspace-2055438/