Unusable Indexes

A very common historical technique for loading data into a table efficiently was to

• drop the indexes
• truncate the table
• load the data
• recreate the indexes

Way back in version 8.0, Oracle gave us the facility to set an index to UNUSABLE so that we would not have to remember the DDL that we used to the create the indexes in the first place. So the process then became

• set the indexes to unusable
• truncate the table
• load the data
• rebuild the indexes

However, you're probably wondering why that load is taking so long. Here's why...

SQL> drop table t;

Table dropped.

SQL> create table t ( x number );

Table created.

SQL> create index tx on t ( x );

Index created.

SQL> alter index tx unusable;

Index altered.

SQL> select status from user_indexes
  2  where table_name = 'T';

STATUS
--------
UNUSABLE

SQL> truncate table t;

Table truncated.

SQL> select status from user_indexes
  2  where table_name = 'T';

STATUS
--------
VALID

Ouch! This is actually documented behaviour (its in the v9 and v10 docs). So remember to set your indexes to unusable AFTER you have performed your truncate.