Using the HIGH mode option

Use the HIGH mode option to update the same statistics that you can calculate with the MEDIUM mode option. The difference between UPDATE STATISTICS HIGH and UPDATE STATISTICS MEDIUM is the number of rows sampled.

UPDATE STATISTICS HIGH scans the entire table, while UPDATE STATISTICS MEDIUM samples only a subset of rows, based on the confidence and resolution used by the UPDATE STATISTICS statement.

For indexed tables that already have MEDIUM mode distribution statistics available for every column, the query optimizer typically chooses more efficient execution plans after you run UPDATE STATISTICS HIGH on every column that is part of an index key.

The database server places distribution information in the sysdistrib system catalog table, and in other system catalog tables for fragmented tables that use distributed storage.

If you do not specify a Resolution clause, the default percentage of data distributed to every bin is 0.5, a value that partitions the range of values for each column into approximately 200 intervals.

The constructed distribution is exact. Because more information is gathered, this mode executes more slowly than LOW or MEDIUM modes. If you use the HIGH mode option of UPDATE STATISTICS, the database server can take considerable time to gather the information across the database, particularly a database with large tables. The HIGH mode might scan each table several times for each column. To minimize processing time, specify a table name and column names within that table, rather than accept the default scope of all tables.

Distributions are not calculated, however, for LVARCHAR, BYTE, or TEXT columns.

You must have the DBA privilege or be the owner of the table to create HIGH mode distributions. For more information on the MEDIUM and HIGH mode options, see the topic Resolution Clause.