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>