I am looking to drop a partition from a table in the database.
I wanted to know if I run ALTER TABLE DROP PARTITION UPDATE INDEX, would there be any blocking of DML statement executions on the other partitions as the indexes get updated?
Also, I am looking to drop 38 partitions in total in the table. Should I run the update index statement each time I drop a partition, or drop all 38 partitions and then run the update index statement?
P.S. the index is a global one.
Best Answer
As you know, Partition solution announced to manage heavy data without blocking or high IO operation. There are various scenarios, I'm going to explain you:
1- Clear data from non-active partitions First you can clear data with Switch out operation, then merge your specific partition, with this, you will drop a partition without any blocking. You don't need to refresh your Indexes with this solution. Notice that, if your partition is Active, you have to wait to other session finished first(It's blocking). Actually you've not dropped active partitions.
2- Drop table partitioning with this operation, you have to recreate table, then you need to recreate your indexes too. But for other situation you don't need to rebuild, reorganize your indexes. Tip : You have to maintenance data Fragmentation of active partitions, in regular schedule.