Unusable Indexes

I tested this post on an Oracle 9 database. It shows that if you have an index which is marked as UNUSABLE, truncating the underlying table makes it VALID again.

First I created a table:

SQL> CREATE TABLE ANDREWS_TABLE AS
  2  SELECT * FROM DBA_TABLES
  3  /

Table created.

SQL>

Then I added an index to the table:

SQL> CREATE INDEX ANDREWS_INDEX
  2  ON ANDREWS_TABLE(TABLE_NAME)
  3  /

Index created.

SQL>

... and made it UNUSABLE:

SQL> ALTER INDEX ANDREWS_INDEX UNUSABLE
  2  /

Index altered.

SQL> SELECT STATUS
  2  FROM USER_INDEXES
  3  WHERE INDEX_NAME = 'ANDREWS_INDEX'
  4  /

STATUS
--------
UNUSABLE

SQL>

I truncated the table:

SQL> TRUNCATE TABLE ANDREWS_TABLE
  2  /

Table truncated.

SQL>

... and this made the index valid again:

SQL> SELECT STATUS
  2  FROM USER_INDEXES
  3  WHERE INDEX_NAME = 'ANDREWS_INDEX'
  4  /

STATUS
--------
VALID

SQL>