Does index unusable drops the storage segment?

Answer 10g No, 11gR2 Yes

10g:-

SQL> select * from v$version;

BANNER
-------------------------------------------------------
Oracle Database 11g Release 10.2.0.5.0 - Production
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 32-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table test as select rownum id, 'TEST' name from dual connect by level <= 1000000;
Table created.

SQL> create index TEST_IDX on test(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TEST', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

SQL> alter index TEST_IDX unusable;
Index altered.

SQL> select index_name, blevel, leaf_blocks, num_rows, status, dropped from dba_indexes where index_name = 'TEST_ID_I';
INDEX_NAME     BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DRO
---------- ---------- ----------- ---------- -------- ---
TEST_ID_I          2        2226    1000000 UNUSABLE  NO

SQL> select segment_name, bytes, blocks, extents from dba_segments where segment_name = 'TEST_ID_I';
SEGMENT_NAME      BYTES     BLOCKS    EXTENTS
------------ ---------- ---------- ----------
TEST_ID_I     18874368       2304         18

Note above, the Segment is not dropped.

Same test repeat in 11g:-

SQL> select * from v$version;

BANNER
-------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table test as select rownum id, 'TEST' name from dual connect by level <= 1000000;
Table created.

SQL> create index TEST_IDX on test(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TEST', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

SQL> alter index TEST_IDX unusable;
Index altered.

SQL> select index_name, blevel, leaf_blocks, num_rows, status, dropped from dba_indexes where index_name = 'TEST_ID_I';
INDEX_NAME     BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DRO
---------- ---------- ----------- ---------- -------- ---
TEST_ID_I          2        2226    1000000 UNUSABLE NO

SQL> select segment_name, bytes, blocks, extents from dba_segments where segment_name = 'TEST_ID_I';
no rows selected

This feature is useful to save the storage when you large partition indexes etc which are unused at all. But be aware this may consume more time of index rebuild when you try to mark usable back.

There is also a bug :- Bug 10258337 - Unusable index segment not removed for "ALTER TABLE MOVE" [ID 10258337.8]

-Thanks

Geek DBA