Friday, June 10, 2011

Rolling partition and global index - do we need to rebuild ?

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 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.


Niall said...

Definitely read Richard before me. He's the expert.

DomBrooks said...

Hi Martin,

I've been investigating a performance problem with an statement which, once traced, showed a large number of slow db file sequential reads on the indexes on the table being inserted (indexes not part of the statement execution plan but obviously insert requires index maintenance and unique keys require validation).

This was a partitioned table - partitioned by range (daily date), and then sub partitioned by list. There was a global unique index - a composite index - on this table which included the partition key in position 3 I think but did not include the subpartition key. Maintenance to drop off the old partitions using "update global indexes" was done at the weekend.

To cut a long story short, it fits with the situation you've described.