3

I am using Oracle 11g.

I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt.

ALTER TABLE ABC DROP PARTITION PART1 UPDATE GLOBAL INDEXES;

But If I break the query into 2 parts and rebuilt indexes with ONLINE option separately, DML queries DOES NOT get blocked while indexes are being rebuilt

ALTER TABLE ABC DROP PARTITION PART1;
ALTER INDEX XYZ REBUILD ONLINE;

The down side of this approach is the time between the execution of drop partition and rebuilding index, those indexes will be unusable that might create performance problems.

So my question is, is there any option where I can drop partition and rebuild index "online" in one query? Currently I dont think we have following option.

ALTER TABLE ABC DROP PARTITION PART1 UPDATE GLOBAL INDEXES ONLINE;
5

Not in 11g.

Not even in 12c, but 12c makes life a bit easier, you can delay the "rebuild" part until a more appropriate time.

In 12c there is a new feature, called Asynchronous Global Index Maintenance. You can drop the partition, and that still leaves the global index usable, and marks the no longer needed index entries orphaned. Then later, you can clean up this index (DBMS_PART.CLEANUP_GIDX, or ALTER INDEX COALESCE CLEANUP, or the database can do it automatically for you as well (there is a pre-defined job for it called PMO_DEFERRED_GIDX_MAINT_JOB).

  • Ok. thanks for the info. One question is in 11g when the indexes are unusable for brief period of time, are insert statements expected to fail? I could not reproduce this but thats what I read online – Sauchin Feb 2 '16 at 23:10
  • 1
    @Sauchin No, inserts don't fail because of an unusable index. The index is simply not maintained. – Balazs Papp Feb 3 '16 at 6:34
  • You should make sure that your partitioned tables have primary keys. There is an issue with dropping partitions on tables that lack primary keys making global indexes invalid. – Gandolf989 Feb 3 '16 at 15:02
  • Will double check on the primary key. Thanks for the information :) – Sauchin Feb 3 '16 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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