11gR2修改index状态为unusable，则删除index的segment

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>