Does ‘update global indexes’ not work if the index is already unusable

indexoraclepartitioning

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.