Composite partitioned table purge causes need for index rebuild

In a critical 24/7 database we have a very large transactional table which is partitioned by date (weekly) and then further subpartitioned by list (we have 3 subpartitions per week). The nature of the list partitioning means we have one very large subpartition and 2 much smaller subpartitions for every week.

The current purge strategy is to delete data from the 'large' subpartition for any weekly partitions older than 6 weeks. This causes the global (primary key) index to be much much large than it needs to be because the some index leaf blocks are having almost all of their entries deleted (leaving a very small number of rows and loads of empty space).

So I've decided to rebuild the index to reclaim 90% of the wasted space because this is causing the index datafile to grow uneccessarily. Rebuilding the index shrinks the index size down from 30GB to 4GB.

However the uptime of this system is essential so I am being paranoid about causing an unplanned outage. I've tested the online index rebuild in a staging environment and I'm happy with it. No transactions failed during testing and the rebuild finished in less than 30 minutes.

Anyone have any thoughts about what other potential risk areas I need to check to make sure this change doesn't cause any problems.

The rebuild will happen in a relatively quiet window when the transaction load is 3 per second.

Thanks Received on Sun Sep 13 2009 - 21:10:27 CDT