unusable index and truncate table

Changing index to unusable will drop the index segment but if we truncate the table, it will make index usable again.

ALTER INDEX X2F2TEST_UNUSABLE UNUSABLE

select status,VISIBILITY,SEGMENT_CREATED,index_name from user_indexes a where index_name='X2F2TEST_UNUSABLE'

will retuen
UNUSABLE VISIBLE NO X2F2TEST_UNUSABLE

select * from dba_segments where segment_name='X2F2TEST_UNUSABLE'
will return no rows. It show's that segment dropped.

So if we have index unusable, it depends if oracle maintains index during DML operations or not based on skip_unusable_indexes initialization parameter. If it is true it won't be maintained, else (don't use FALSE ever)it will throw error during DML operation that unusable index exists or during select if that select usage that index.

Truncating a table makes an unusable index valid. So if we truncate a table post changing it to unusable, it will change status of index to valid.

truncate table X2F2TEST_UNUSABLE;

select status,VISIBILITY,SEGMENT_CREATED,index_name from user_indexes a where index_name='X2F2TEST_UNUSABLE'
will retuen
VALID VISIBLE YES X2F2TEST_UNUSABLE

To make an unusable index usable, we have to rebuild the index.

ALTER INDEX scpomgr.X2F2TEST_UNUSABLE rebuild parallel 5

ALTER INDEX scpomgr.X2F2TEST_UNUSABLE  noparallel

Unusable Index:When you make an index unusable it is ignored by optimizer and not updated by dml operation. After you make index unusable.It must rebuilded before using it.

Invisible Index:When you make index invisible it is ignored by optimizer.You can use this for test purpose.you can make index  invisible when you create index or with alter index command after creating table . Hovewer if you set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE invisible  index can be used by optimizer.This parameter can be set session or system level.
NOTE:When you make index invisible this will result invalidate all sql  statement in the shared memory that have execution plan using this index