Partitioned tables and statistics

Elsewhere a question popped up relating to binds on partition keys. David Aldridge ran some tests (also posted here) that indicated that binds would use global table stats rather than partition stats. Of course this is intuitive; the partition is not known at parse time and is only resolved at run time so the only sensible thing the optimiser can do is to use the global stats. But in the case of a bind on the partition key we would expect 100% of the rows accessed to be in a single partition and not as global stats may infer spread across many other partitions so we may well get a 'poor' plan.

That aside it reminded me of a quirk of partition maintenance that impacted one of systems we support a few years back (in 8.1.7 days). We had a 3TB system with 115 weekly partitions on the day level fact tables - that is 7 days per partition, further more it was then hash sub-partitioned on product. Here, I would like to say this was not my design - as I posted a while back, I loath hashing products as it does not reduce IO except in the very rare single product query. But I digress, each week the support team would run some scripts to drop the oldest weekly partition and create a new one to receive the next weeks data. The support team were efficient and made sure that the script ran well in advance of the weekend when the new week was needed. As soon as the partition maintenance had occurred support calls started to be come in for slow running queries. We managed to quickly tie down the relationship between maintenance and bad performance - it took a bit longer to see that the problem was caused by an empty partition (with no stats) causing the global stats to break. The fix was to collect stats on the empty partition as part of the maintenance.