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.
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 (
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.
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.
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?