2

I have created a monthly partition on my table like below:

PARTITION BY RANGE (PARTITION_KEY) 
     INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
   (  
     PARTITION MayPartition VALUES LESS THAN (TO_DATE('01/06/2016', 'DD/MM/YYYY'))
   );

Since my table has data in TB, dropping this monthly partition is taking more than 10 hours for 1 month.

Is it possible to drop partition for only 15 days? Or is there a way to reduce the time taken to drop partition?

I made sure there was no other load on database while dropping the partition.

I am using this command to drop the partition:

alter table table_name drop partition partition_name update indexes

I have global indexes on the table. I don't have foreign keys referencing the PK of that table.

1
  • 1
    @JSapkota why would that be better? May 31 '16 at 11:30
1

If it was me, I would partition by hour, then you can just drop 24 partitions every day. That is only 744 partitions per month. Then if you query within a relatively short window, you will look at much less data. You may also want to look at your index strategy. My guess is that the cost of maintaining the indexes is too high. IMHO, smaller partitions should help speed up the queries and make it easier to maintain the partitions.

2
  • 2
    Likely there are global indexes causing huge overhead. Even with smaller partitions, a huge amount of effort would be expended traversing them to delete the millions of rows required. May 31 '16 at 14:20
  • That is possible. I have seen issues related to not having a primary key on a partitioned table causing global indexes to be unusable after dropping a partition. Hopefully, a table with TB of data has a primary key.
    – Gandolf989
    May 31 '16 at 17:31

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.