Partitioned tables and update statistics

Did you know that if you have a partitioned table, that running your normal update statistics or update index statistics does not update the statistics for the partitions?  Many dbas forget to perform update partition statistics on their partitions.  Sadly, neither Sybase or Rob Verschoor say much about it.  This is rather surprising as Rob is full of useful information about such things.

Of course, you could just run update all statistics but the overhead of maintaining the statistics is a nightmare and I have yet to find any real reason to do so.

Syntax (v12.x): 

update partition statistics table_name  [partition #]

Syntax (v15.x):

update table statistics table_name [partition name]
Share Button

2 Replies to “Partitioned tables and update statistics”

  1. When you gather partition statistics in ASE do they roll up to the table level? Oracle came out with incremental statistics in 11g and – if you do it just right – the partition statistics roll up to the table level and produce excellent statistics. I presented on this topic at the Rocky Mountain Oracle Users Group so I’m very familiar with how it works in Oracle. It isn’t perfect but it’s awfully good.

    1. It is my understanding that each partition would have its own set of statistics if using update statistics with the partition option. If you do it for every partition, then it would presumably be near a full table statistics as far as the optimizer is concerned. I haven’t tested this conclusively but that’s the way it looks with 15.7 esd 4.

Leave a Reply

Your email address will not be published. Required fields are marked *