Adaptive Server versions 15.7 ESD #2 and later allow you to gather hash-based
statistics on minor index attributes and unindexed columns instead of using sort-based
statistics, significantly reducing elapsed time and resource usage. Using hash-based
statistics improves performance by reducing the number of required scans, and avoiding
disk-based sorting.
Hash-based statistic allow greater flexibility than sort-based statistics:
- Running hash-based statistics should require less time, increasing the amount
you can accomplish during a maintenance window.
- Because hash-based statistics require less procedure cache, you may be able to
run update statistics on a data-only-locked table outside a
maintenance window, since the Adaptive Server tempdb buffer
cache (which typically uses the default data cache) is typically much larger
than the procedure cache, reducing the impact of update
statistics.
- Hash-based statistics do not generally require large tempdb
disk allocations. If you previously increased the size of
tempdb to accommodate large sorts from update
statistics, you may be able to redeploy this space.
- update [index | all] statistics with hashing may run faster
than update [index | all] statistics with sampling. However,
an exception may be update statistics
table_name(col_name).
- update statistics table_name
(col_name1), (col_name2) . . . with hashing allows you to
collect statistics on several columns with a single scan instead of several
scans.