Gathering Hash-Based Statistics with create index

Adaptive Server 15.7 SP100 extends the support of hash-based statistics gathering to the create index command.

The create index command hash-based statistics gathering options allow minor index attributes to be gathered while the index is being created serially or in parallel. With hash-based statistics gathering enabled, it is not necessary to update statistics after creating the index in regards to minor index attributes.

Use the following create index parameters to gather hash-based statistics. The syntax is:

create [unique] [clustered | nonclustered] index index_name
	on database.]owner.]table_name
	[with {... 
            [, statistics {no_hashing | new_hashing | hashing}]                       
            [, statistics max_resource_granularity = int] 
            [, statistics histogram_tuning_factor =int] 
            [, statistics print_progress = int] } ] 
	...}

Enabling Hash-Based Statistics Gathering

Use with statistics hashing to indicate that an index is to be created, and minor index attributes are to be gathered using hash-based statistics. Use with statistics no_hashing to indicate that no hash-based statistics are to be gathered. Index-leading column statistics that are gathered using sort-based algorithm from versions of Adaptive Server versions earlier than 15.7 ESD #2 are not impacted by the with statistics hashing options.

sp_configure Option

A new sp_configure option, utility statistics hashing is introduced in Adaptive Server 15.7 SP100. For option settings, see sp_configure in the System Changes > Procedures > Changed System Procedures section.

If the clauses no_hashing, new_hashing, or hashing have not been specified in the create index command, the value of utility statistics hashing is used.

Precedence of Existing sp_configure Options

Any explicit phrases used in create index with statistics histogram_tuning_factor take precedence over the sp_configure histogram_tuning_factor.

Any explicit phrases used in create index with statistics max_resource_granularity take precedence over the sp_configure max_resource_granularity.

Examples

Example 1

Creates a unique clustered index named au_id_ind on the au_id and title_id columns of the authors table. Statistics are gathered with hashing, counts 50 steps, and enables print_progress which shows progress messages:

create unique clustered index au_id_ind
    on authors(au_id, title_id)
    with statistics hashing,
        statistics using 50 values,
        statistics print_progress = 1

Example 2

Creates an index named ind1 on the au_id and title_id columns of the titleauthor table. Statistics are gathered with hashing, counts 50 steps, and sets the percentage of system resources a query can use at 80 percent using the max_resource_granularity option:

create index ind1
    on titleauthor (au_id, title_id)
    with statistics using 50 values,
        statistics hashing,
        statistics max_resource_granularity = 80

Example 3

Creates a nonclustered index named zip_ind on the postalcode and au_id columns of the authors table. Each index page is filled one-quarter full and the sort is limited to 4 consumer processes. Statistics are gathered with hashing, counts 50 steps, and generates an intermediate 40-step histogram:

create nonclustered index zip_ind
    on authors(postalcode, au_id)
    with fillfactor = 25,
        consumers = 4,
        statistics using 50 values,
        statistics hashing,
        statistics histogram_tuning_factor = 40

Note: You can also use sp_configure to set max_resource_granularity and histogram_tuning_factor. Hash-based statistics settings in the create index command take precedence over any values set by sp_configure.

Example 4

Creates a unique clustered index named au_id_ind on the au_id and title_id columns of the authors table. Statistics are gathered with hashing for minor attributed columns that have not had statistics previously gathered:

create unique clustered index au_id_ind
    on authors(au_id, title_id)
    with statistics new_hashing