I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with


the global index has the status UNUSABLE.

If I use


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.

1 Answer 1


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.

  • Thanks a lot, this explains it perfectly. So I misunderstood the option, I thought it really has the same effect as running a 'alter index idx1 rebuild', apparently this is wrong.
    – pgruetter
    Mar 21, 2013 at 10:44
  • Yes, it is quite different. You'll need to do some timing tests to determine whether the benefits of "update indexes" are worth it for you. (Do check that doc, especially the "Considerations when Updating Indexes Automatically" section, there is interesting information there.)
    – Mat
    Mar 21, 2013 at 10:47
  • Also reconsider whether local indexes are better for you situation.
    – Philᵀᴹ
    Mar 21, 2013 at 10:49
  • @Mat: Will do that. The doc is interesting, thanks!
    – pgruetter
    Mar 21, 2013 at 10:54
  • @Phil: True, we wouldn't have these problems with local indexes. However, I need a PK on the whole table in this case so we are bound to a global index.
    – pgruetter
    Mar 21, 2013 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.