Like the update statistics command for unpartitioned tables, the update partition statistics command helps Adaptive Server make the best decisions when it processes a query, by keeping it up to date about the number of pages within the partitions. Use update partition statistics when a large amount of data in a partitioned table has been added, changed, or deleted.
Permission to issue the update partiton statistics command defaults to the table owner and is not transferable. Its syntax is:
update partition statistics table_name
For example, suppose the authors table was partitioned as follows:
alter table authors partition 3
Then you run sp_helpartition to see how the partitions were distributed:
sp_helpartition authors
partitionid firstpage controlpage ptn_data_pages
----------- ----------- ----------- --------------
1 553 554 1
2 817 816 1
3 1009 1008 1
(3 rows affected, return status = 0)
Afterwards, you update the statistics for authors as follows:
update partition statistics authors
Using sp_helpartition on authors shows the following update:
partitionid firstpage controlpage ptn_data_pages
----------- ----------- ----------- --------------
1 553 554 10
2 817 816 1
3 1009 1008 1
(3 rows affected, return status = 0)
Dropping and re-creating a clustered index automatically redistributes the data within partitions and updates the partition statistics.
