11gR2修改index状态为unusable,则相应的segment将会被删除,测试如需:
SQL> create table MY_OBJECTS as select * from dba_objects;
Table created.
SQL> create index ind_object_id on my_objects(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('','MY_OBJECTS');
PL/SQL procedure successfully completed.
SQL> select index_name,status from user_indexes where index_name='IND_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ --------
IND_OBJECT_ID VALID ------开始索引状态为VALID
SQL> select segment_name,blocks from dba_segments where segment_name='IND_OBJECT_ID';
SEGMENT_NAME BLOCKS
---------------------- ----------
IND_OBJECT_ID 40 ------ segment存在
SQL> alter index ind_object_id unusable; ------修改index的状态为unusable
Index altered.
SQL> select index_name,status from user_indexes where index_name='IND_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ --------
IND_OBJECT_ID UNUSABLE
SQL> select segment_name,blocks from dba_segments where segment_name='IND_OBJECT_ID';
no rows selected ------ segment被删除
truncate含有unusable索引的表,因为牵扯表重建,unusable的索引将会被重建,相应的segment将分配。
SQL> truncate table MY_OBJECTS;
Table truncated.
SQL> select index_name,status from user_indexes where index_name='IND_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ --------
IND_OBJECT_ID VALID
SQL> select segment_name,blocks from dba_segments where segment_name='IND_OBJECT_ID';
SEGMENT_NAME BLOCKS
---------------------- ----------
IND_OBJECT_ID 8 ------segment已分配
SQL>