cancel
Showing results for 
Search instead for 
Did you mean: 

What does sp_configure "update statistics hashing" do? (ASE 16)

sladebe
Active Participant
0 Kudos

For ASE 16, I don't have this config option set, but I can still run update statistics with a explicit "with hashing" clause.

The docs for this just say "enables SAP ASE to gather hash-based statistics"

sladebe
Active Participant
0 Kudos

Ok, got it. The "update statistics hashing" controls the default behavior. It would be nice if the docs actually said that...

Re: You can see this behavior by running a series of tests with different config settings and update (index) statistics commands, and searching optdiag output for indications of hashing use.

When using the "update index statistics" or "update all statistics" command with the "print_progress=1" option, you can see hashing based stats updates:

[110] MYSERVER.mydb-11:27:24-1> update index statistics mytable with hashing, print_progress=1;
Update Statistics STARTED.
Update Statistics index scan started on index 'myindex'.
...It is using existing scan to hash column 'year' (column id = 2).
...It is using existing scan to hash column 'volume' (column id = 3).
...It is using existing scan to hash column 'issue' (column id = 4).
...It is using existing scan to hash column 'month' (column id = 5).
Update Statistics table scan started on table 'mytable' for summary statistics.
Update Statistics FINISHED.

Note that if you use the "update index statistics <table> with partial_hashing, sampling=10 percent" (for example), it will use hashing for low ordinality columns (low number of distinct values) and sampling with sorting for high ordinality columns.

former_member89972
Active Contributor
0 Kudos

Cool Ben

Noted and will try to use the ""print_progress=1" option" for our index stats runs.

Thanks for sharing the tip.

Avinash

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member89972
Active Contributor
0 Kudos

Hi Ben

Is it a case of syntax you used is valid and therefore allowed to run

but the actual gathering based on hashing is silently ignored ?

It will be interesting if we have inputs from SAP/Sybase

on checks/tests to run before and after the update stats with hashing option to verify that it did what it claimed to do.

Avinash

sladebe
Active Participant
0 Kudos

Re: Is it a case of syntax you used is valid and therefore allowed to run but the actual gathering based on hashing is silently ignored ?

No, as discussed above, the "update statistics hashing" config parameter just controls the default behavior (of whether to use hashing for update stats commands). If you explicitly specify a "with hashing" option, it is used.