How to avoid rebuild of indexes if DDL performed on partitioned table

On the partitioned table, if we don't do anything with the index then the index against the partitioned table remain global Non partitioned indexes and thus whenever we perform any DDL operation against the table partition like drop a partition or truncate a partition or add a partition or merge a partition or split a partition then the associated global indexes become invalid. Thus after doing DDL we need to rebuild the indexes. This may be very cumbersome and it may need several hours if the table is big enough.

With an example I am demonstrating how global indexes against a table become invalid if I do any DDL against the table.

1)Create a partitioned table.

CREATE TABLE test_partition
 (
 id number,
 created_date  date,
 col3 varchar2(20)
 )
 PARTITION BY RANGE (created_date)
    (
     PARTITION part1 VALUES LESS THAN (to_date('11-oct-2008','dd-mon-yyyy')) ,
     PARTITION part2 VALUES LESS THAN (to_date('12-oct-2008','dd-mon-yyyy')) ,
     PARTITION part3 VALUES LESS THAN (to_date('13-oct-2008','dd-mon-yyyy')) ,
     PARTITION part4 VALUES LESS THAN (to_date('14-oct-2008','dd-mon-yyyy')) ,
     PARTITION junk VALUES LESS THAN (MAXVALUE)
   )
 /
Table created.

2)Insert data into the partitioned table.

insert into test_partition
    select rownum, to_date('10-oct-2008','dd-mon-yyyy')+mod(rownum,7),
           rownum
      from dba_users;

41 rows created.

3)Create index on the partitioned table.
SQL> create index tpid_i on test_partition(id);
Index created.

4)Be sure partitioned is done successful.
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
6

Let's see some sample data.

SQL> select * from test_partition partition (part2);
        ID CREATED_D COL3
---------- --------- --------------------
         1 11-OCT-08 1
         8 11-OCT-08 8
        15 11-OCT-08 15
        22 11-OCT-08 22
        29 11-OCT-08 29
        36 11-OCT-08 36

6 rows selected.

5)Let us see the status of the index against the partitioned table.
Note that since we have made global non-partitioned indexes so we will not get any entry about valid/unusable index in view user_ind_partitions. To get index status we have to query from user_indexes view.

SQL> select partition_name, status from user_ind_partitions where
index_name = 'TPID_I';
no rows selected

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

6)Do some DDL operation on the partitioned table.
SQL> alter table test_partition truncate partition part2;
Table truncated.

Check by,
SQL> select count(*) from test_partition partition (part2);

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

7)Check the index status now.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         UNUSABLE

Since we have global index so any DDL under the table marks the index unusable. If you want to make the index valid then you have to rebuild the index.

SQL> alter index tpid_i rebuild;
Index altered.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

This rebuild process really time consuming. And for production server it may not be tolerable.

8)The solution is to make the index as local partitioned index.
SQL> drop index tpid_i;
Index dropped.


SQL> create index tpid_i on test_partition(id) local;
Index created.

Check status by,

SQL>  select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

Since it is local partitioned index we have to query from user_ind_partitions.
SQL> select partition_name, status from user_ind_partitions where
2 index_name = 'TPID_I';

PARTITION_NAME STATUS
------------------------------ --------
JUNK USABLE
PART1 USABLE
PART2 USABLE
PART3 USABLE
PART4 USABLE

SQL> select count(*) from test_partition partition (part3);
COUNT(*)
----------
6

9)Let's do some DDL operation and test.
SQL> alter table test_partition truncate partition part3;
Table truncated.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

SQL>  select partition_name, status from user_ind_partitions where
  2      index_name = 'TPID_I';

PARTITION_NAME                 STATUS
------------------------------ --------
JUNK                           USABLE
PART1                          USABLE
PART2                          USABLE
PART3                          USABLE
PART4                          USABLE

And it still remains in USABLE status. So you might able to save a lots of time as well as non-interrupt service and effort against it.