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>

Truncate (Part 1)

This post shows the effect of the TRUNCATE statement. First create a table:

SQL> create table truncate_example
  2  as select * from dba_tables
  3  /

Table created.

SQL>

Count the lines in the table:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
      3023

SQL>

And the number of extents:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Truncate the table with the REUSE STORAGE option:

SQL> truncate table truncate_example reuse storage
  2  /

Table truncated.

SQL>

Now count the number of rows in the table. There will be none:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Truncate does not produce any undo information so if you do a rollback, the deleted lines will not reappear:

SQL> rollback;

Rollback complete.

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Now count the number of extents. It will be the same as before because you included the REUSE STORAGE clause:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Now reinsert the deleted rows from the source table:

SQL> insert into truncate_example
  2  select * from dba_tables
  3  /

3024 rows created.

SQL>

Truncate the table again but this time, add the DROP STORAGE clause, which is the default:

SQL> truncate table truncate_example
  2  drop storage
  3  /

Table truncated.

SQL>

Count the number of rows in the table again. There will be none, as before:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

And count the number of extents. By adding the DROP STORAGE clause, the table’s extents have been deallocated. The number of extents remaining is determined by the table’s MINEXTENTS value:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
         1

SQL>