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.
