A new thing I learned today: Truncating a table makes an unusable index valid

 Today I was rereading 11gR2 new Features regarding unusable indexes. I met with following in that document: 11GR2 doc

Unusable indexes

An unusable index is ignored by the optimizer and is not maintained by DML. One reason to make an index unusable is if you want to improve the performance of bulk loads. (Bulk loads go more quickly if the database does not need to maintain indexes when inserting rows.) Instead of dropping the index and later recreating it, which requires you to recall the exact parameters of the CREATE INDEX statement, you can make the index unusable, and then just rebuild it. You can create an index in the unusable state, or you can mark an existing index or index partition unusable. The database may mark an index unusable under certain circumstances, such as when there is a failure while building the index. When one partition of a partitioned index is marked unusable, the other partitions of the index remain valid.

An unusable index or index partition must be rebuilt, or dropped and re-created, before it can be used. Truncating a table makes an unusable index valid.

Truncating a table makes an unusable index valid? Interesting. Lets try:

oracle@MG1$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 29 16:46:58 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> create
  2  
SQL> 
SQL> create table mytable1 (x int);

Table created.

SQL> create index mytable1$idx on mytable1(x);

Index created.

SQL> insert into mytable1 select 1 from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select status from dba_indexes where index_name = 'MYTABLE1$IDX';

STATUS
--------
VALID

SQL> alter index MYTABLE1$IDX unusable;

Index altered.

SQL> truncate table mytable1;

Table truncated.

SQL> select status from dba_indexes where index_name = 'MYTABLE1$IDX'
  2  /

STATUS
--------
VALID

Exactly!

I tried looking for the same information in previous release documentations (even in 9i), however I could not. Pls, share with me if you find.

Thanks.