實驗: Oracle中表shrink與move後index的狀態

1 move時實驗

SQL> create table my_objects_move tablespace ASSM as select * from all_objects where rownum < 20000;

Table created

SQL> create index i_my_objects_move on my_objects_move (object_id);

Index created

 

SQL> delete from my_objects_move where object_name like '%C%';

7546 rows deleted

SQL> delete from my_objects_move where object_name like '%U%';

2959 rows deleted

SQL> commit;

Commit complete

 

SQL> alter table my_objects_move move;

Table altered

SQL> select index_name, status

  2    from user_indexes

  3   where index_name = 'I_MY_OBJECTS_MOVE';

INDEX_NAME                     STATUS

------------------------------ --------

I_MY_OBJECTS_MOVE             
UNUSABLE

SQL> alter index I_MY_OBJECTS_MOVE rebuild;

Index altered

 

SQL> select index_name, status

  2    from user_indexes

  3   where index_name = 'I_MY_OBJECTS_MOVE';

INDEX_NAME                     STATUS

------------------------------ --------

I_MY_OBJECTS_MOVE             
VALID

2 shrink時實驗

SQL> create table my_objects tablespace ASSM as select * from all_objects where rownum < 20000;

Table created

SQL> create index i_my_objects on my_objects (object_id);

Index created

 

SQL> delete from my_objects where object_name like '%C%';

7546 rows deleted

SQL> delete from my_objects where object_name like '%U%';

2959 rows deleted

SQL> commit;

Commit complete

 

SQL> alter table my_objects enable row movement;

Table altered

SQL> alter table my_objects shrink space;

Table altered

 

SQL> select index_name, status

  2    from user_indexes

  3   where index_name = 'I_MY_OBJECTS';

INDEX_NAME                     STATUS

------------------------------ --------

I_MY_OBJECTS                  
VALID

3 結論

shrink在移動行資料時，也一起維護了index上相應行的資料rowid的資訊，當然shrink過程中用來維護index的成本也會比較高。而表move後index的狀態是UNUSABLE的,需要進行rebuild。