Use this syntax to create hash-based statistics:
update index statistics table_name [[partition data_partition_name] | [ [index_name [partition index_partition_name]]] [using step values] [with consumers = consumers] [, sampling=N [percent]] [, no_hashing | partial_hashing | hashing] [, max_resource_granularity = N [percent]] [, histogram_tuning_factor = int ] update all statistics table_name [partition data_partition_name] [using step values] [with consumers = consumers] [, sampling=N [percent]] [, no_hashing | partial_hashing | hashing] [, max_resource_granularity = N [percent]] [, histogram_tuning_factor = int ] update statistics table_name [[partition data_partition_name] [ (col1, col2, …) | (col1), (col2), …] | [ [index_name [partition index_partition_name]]] [using step values] [with consumers = consumers] [, sampling=N [percent]] [, no_hashing | partial_hashing | hashing] [, max_resource_granularity = N [percent]] [, histogram_tuning_factor = int]
Determine the level of hashing with [no_hashing | partial_hashing | hashing]:
no_hashing – uses the sort algorithm from versions of Adaptive Server earlier than 15.7 ESD #2.
partial_hashing – Adaptive Server uses hashing for low unique count domains. If Adaptive Server encounters unique column counts that exceed the 65536 threshold, it uses an extra scan with a sort. Sorts are also used if the previous histogram generated on that column indicated that the number of unique values was greater than or equal to 65536.
The default for these parameters is the configured value for update statistics hashing.
See Reference Manual: Commands.
This example gathers hash-based statistics for the authors table:
update index statistics authors with hashing
Explicitly specifying hash-based statistics in an update statistics command takes precedence over the value for the update statistics hashing configuration parameter. In the example above, update statistics ... with hashing takes precedence over the server-level update statistics hashing parameter.
You cannot use the consumer and sampling parameters when you gather hash-based statistics. Adaptive Server attempts to use low-domain hash-based statistics with partial_hashing before defaulting to the sort-based statistics, which support the consumer and sampling parameters.
Hash-based update statistics allow you to specify column sets as a comma-separated list of column names in parentheses:
update statistics table_name (column1), (column2), (column3), ...
This syntax allows Adaptive Server to perform a single table scan that updates statistics on all columns. However, Adaptive Server issues an error message if you include multiple columns within the same parentheses to gather hashing statistics.
