I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate querying by column needle. This is simple enough.

The issue arises at the end of the month when the oldest table partition is dropped. I can't afford to rebuild a global index on a 3 TB table every month. Only the four most recent month partitions of haystack are in the database at any given time. So roughly 25% of the table is removed when dropping a monthly partition.

Attempted Solutions

I've tried creating an index on column needle which was global partitioned by hash on needle. But this means having to rebuild the entire global index when dropping an old month partition from the table. No good.

I've also tried creating an index on columns (date, needle) which was global partitioned by range on date with ranges that match the table's monthly partitions. But again, I'm still forced to update my entire global index when dropping that old month partition from the table - even though my table and index are partitioned identically (excepting the table's subpartitions).

Of course, I can't simply create a locally partitioned index. I would need the partitions to be local but without the subpartitions. From what I can tell, that's not possible; it's an all or nothing choice with the local partitioning option on indexes.

Dropping Partitions

For completeness, here's a few options I've tried for dropping old partitions at the end of the month...

alter table HAYSTACK drop partition P_MONTH update global indexes; This takes far too long, as it attempts to maintain the global indexes during the drop.

alter table HAYSTACK drop partition P_MONTH; Without the global index clause, my global index is left with all of its partitions marked unusable. (Understandably so.)

alter index HAYSTACK_IDX drop partition P_MONTH; Dropping my index partition invalidates the partition whose high value was just above the dropped partition's. If I were to go on and drop the table partition now, I'm still going to have to rebuild my entire global index.

My Question

Is there a way I can purge old table partitions without invalidating or rebuilding the global index partitions? Essentially, can I somehow tell Oracle to handle these global index partitions as if they were local, but without subpartitioning? (Sadly, my research says no.)

Or... Is there another (better?) way to solve my problem?

  • Did you consider the GLOBAL PARTITION of an index: The global_partitioned_index clause lets you specify that the partitioning of the index is user defined and is not equipartitioned with the underlying table. Can you define the index partitioned using the same rule as underlaying table? May 28, 2015 at 20:15
  • My entire question is based on an index with global partitioning... One of the attempted solutions I already outlined in my post was to create index global partitions that mimic the table partitions, as you suggest. This does work well for accessing the data quickly. But, as my post states, that is not something my system can maintain when I need to drop old partitions.
    – MollyOQ
    May 28, 2015 at 20:31

1 Answer 1


version <= 11.2? No.*

But in 12c, there is a new feature called Asynchronous Global Index Maintenance.

The DROP PARTITION operation completes immediately, leaving the global index in usable state. The trick is, that index entries are not maintained, they are orphaned, and these orphaned entries will be cleaned up later. The cleanup process happens automatically in the maintenance window, or it can be started manually.

More details about this: https://richardfoote.wordpress.com/category/asynchronous-global-index-maintenance/

*: There is, but I don't think it is applicable in your case. Deleting (and NOT truncating) all rows from the partition before dropping it leaves the global index in usable state. But this is enormous extra work for the 25% of a 3 TB table.

  • Thanks! This unfortunately doesn't help me now, but we do plan on upgrading to 12c be the end of the year. Cheers
    – MollyOQ
    Jun 1, 2015 at 14:15

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.