Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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. –  socken23 Mar 21 '13 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 '13 at 10:47
    
Also reconsider whether local indexes are better for you situation. –  Phil Mar 21 '13 at 10:49
    
@Mat: Will do that. The doc is interesting, thanks! –  socken23 Mar 21 '13 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. –  socken23 Mar 21 '13 at 10:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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