I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with
ALTER TABLE tbl1 TRUNCATE PARTITION p1;
the global index has the status UNUSABLE.
If I use
ALTER TABLE tbl1 TRUNCATE PARTITION p1 UPDATE GLOBAL INDEXES;
the global index is correctly rebuild and has the status VALID.
But: It seems, that the index is not rebuild if it already is in an unsuable state. Is that correct?
- Create a table with 3 range partitions and a global index
- Insert data into all 3 partitions
- truncate partition WITHOUT updating global indexes
- index is in state unusable
- truncate a second partition WITH updating global indexes
- index is still unusable
From my understanding, after the second truncate partition, the index should be usable again.