Partition Drops is effecting performance adversely...

Hello All,

     We have a production DB that is > 1 TB. So we embarked on an archiving strategy, in which for data in partitioned tables , data that is older than 3 months is exported and the relevant partitions are dropped.

   Our partitions are weekly partitions. We had data that gos back to 2004 , but the most volume is in 2005 (after march). With the archiving effort we were able to free up around 300 GB or roughly around 25%, (so far).

   We run incremental stats on our tables (i,e stats only for the latest partitions) and we dont run Global stats , except for once every 3 months.

   Even though our Month-end Billing Jobs are now going against a smaller data set , they are taking longer. Any ideas why?   If we drop partiions from a table , and run incremental stats only, does that put off the optimizer in some way?

   We cant afford to run Global stats on our big partitioned tables because of time constraints.

   My million dollar question is , after doing a ddl like a partition drop, is it necessary to do Global stats for the table , or should the optimizer be smart enough to know that these partitions no longer exist )and incremental stats suffice/

   Any experience or info would be greatly appreciated.

Thanks
Ravi Received on Thu Sep 29 2005 - 19:57:13 CDT