[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