After you migrate to Adaptive Server 15.0, running update statistics on a specific partition should reduce the time you need to run update statistics in general. Most large tables that take a long time to run update statistics contain a lot of historical data, but few, if any, of these historical rows change, yet update statistics must scan them all. Additionally, when viewed from the perspective of the entire table, even one million rows added to a 500 million row table is only a 0.2 percent change, which suggests that statistics do not need to be updated. However, because these are likely the rows most often used and their distribution heuristics are not included in the range cell densities, it is likely that query optimization suffers.
If you partition the data, you can skip older, static data when you run update statistics after the first time, especially if you partition on a date range. After this first run, use datachange to check the amount of change within the current partition and run update statistics only as necessary. All partitions are named, and if you did not supply one (that is, you used the hash partition syntax), Adaptive Server provides a default name for you, similar to tempdb tables. Use sp_help to identify the system supplied names.
This example is based on a system-supplied name and focuses on the p_partkey column:
select datachange("mytable","part_1360004845", "p_partkey")
go
--------------------------- 100.000000
This is a good candidate for running update statistics on the part_1360004845 partition.
This example allows update statistics to focus on a specific partition (and, in this case, column):
update statistics mytable partition part_1360004845 (p_partkey) go
The reduction in time for update statistics may allow you to create statistics or heuristics on columns that you previously avoided due to time constraints. For example, this example creates a histogram on col1 and densities on the combined col1,col2 pair. :
update statistics mytable (col1, col2)
To update statistic by partition and create a histogram on col2:
update statistics mytable partition part_1360004845 (col1, col2) go update statistics mytable partition part_1360004845 (col2) go
