TRUNCATEing a Table makes an UNUSABLE Index VALID again

Here's something I learned from Jonathan Lewis sometime ago.

If you set an Index to be UNUSABLE but later issue a TRUNCATE TABLE, the Index becomes VALID again --- i.e. the Index gets updated with rows subsequently inserted.

SQL> connect hemant/hemant
Connected.
SQL> drop table target_data purge;

Table dropped.

SQL> create table target_data as select * from source_data where 1=2;

Table created.

SQL> create index target_data_ndx_1      
  2  on target_data(owner, object_type, object_name);

Index created.

SQL> insert /*+ APPEND */ into target_data
  2  select * from source_data; 

367156 rows created.

SQL> commit;

Commit complete.

SQL> col segment_name format a30
SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name like 'TARGET_DATA%'
  4  order by 1;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA                    TABLE                         49
TARGET_DATA_NDX_1              INDEX                         19

SQL> 
SQL> col index_name format a30
SQL> select index_name, status
  2  from user_indexes
  3  where table_name = 'TARGET_DATA';

INDEX_NAME                     STATUS
------------------------------ --------
TARGET_DATA_NDX_1              VALID

SQL> 

So, I have a VALID Index on my Table.

I now make it UNUSABLE and add rows to it.

SQL> alter index target_Data_ndx_1 unusable;

Index altered.

SQL> select status     
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_1';

STATUS
--------
UNUSABLE

SQL> insert /*+ APPEND */ into target_data
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> select index_name, status
  2  from user_indexes
  3  where table_name = 'TARGET_DATA';

INDEX_NAME                     STATUS
------------------------------ --------
TARGET_DATA_NDX_1              UNUSABLE

SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name like 'TARGET_DATA%'
  4  order by 1;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA                    TABLE                        104

SQL> 

Oracle actually drops the Index segment (so you don't see it in USER_SEGMENTS) when it is set to UNUSABLE alhough the Index definition is still present.  The Index doesn't "grow" as the Segment doesn't exist.

Let me TRUNCATE the table.

SQL> truncate table target_data;

Table truncated.

SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name like 'TARGET_DATA%'
  4  order by 1;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA                    TABLE                      .0625
TARGET_DATA_NDX_1              INDEX                      .0625

SQL> select index_name, status
  2  from user_indexes
  3  where table_name = 'TARGET_DATA';

INDEX_NAME                     STATUS
------------------------------ --------
TARGET_DATA_NDX_1              VALID

SQL> 

Immediately after the TRUNCATE TABLE, the Index Segment is instantiated and the Index becomes VALID again.  So inserting rows will update the Index.  My last explicit command against the Index was ALTER INDEX ... UNUSABLE but that seems to be not the current state now !

SQL> insert /*+ APPEND */ into target_data
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name like 'TARGET_DATA%'    
  4  order by 1;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA                    TABLE                        49
TARGET_DATA_NDX_1              INDEX                        19

SQL> 

So, repopulating the Table has expanded the Index again.
.
.
.