Drop 15 days records in monthly partition

oracleoracle-11g-r2partitioning

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.

Best Answer

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.

Related Question