Some time ago (arrghhh - three month ago) I blogged about partition dropping when table has a global index - see. There was a example based on my tests. Now it is time to show some real figures. Both examples are sharing same scenario - about 2 years without touching indexes, 7 partitions are drop once per week. Partition drop is taking up to 6 - 7 hours. This time coalesce was not a option due to free up space requirement but it should help in future to avoid similar situations.
Example number 1
Table has around 562.000.000 rows and one partition has around 13.000.000 rows. Primary key index based on one number column growth to 140 GB and has been rebuilt to 13.9 GB. It is around 10 times less.
Example number 2 - bigger scale
Table has around 2.130.000.000 rows and one partition has around 45.000.000 rows.Primary key index based on one number column growth to 360 GB and has been rebuilt to 40 GB. It is 9 times less.
I put both examples here to show you that running coalesce for global indexes after partition drop can save you a lot of space and if done regularly prevent you from more resource consuming index rebuild.To be honest this is one of a few situations when I have seen any improvement (space wise this time) after index rebuild. But keep in mind that this is a special situation and for most of other cases you don't need to rebuild you indexes - if you think you need read Niall or Richard blog first.