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?
To verify:
- 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.
Best Answer
The
update [global] indexes
option does not rebuild indexes after whatever operation you ran. It maintains indexes during the operation (see Updating Indexes Automatically), so that they remain available throughout.After your first truncate, the index needs to be rebuilt - it can't be updated, it's already "broken" and unavailable. Oracle won't rebuild it automatically for the next operation you do on the table, even if you include the update clause.