Using the MEDIUM mode option

Use the MEDIUM mode option to update the same statistics that you can perform with the LOW mode option,and also generate statistics about the distribution of data values for each specified column.

After UPDATE STATISTICS MEDIUM has been run on a table, the query optimizer typically chooses a more efficient execution plan, compared to the same SELECT statement when only LOW mode column distribution statistics are available for the table. column

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 use the MEDIUM mode option, the database server scans tables at least once and takes longer to execute on a given table than the LOW mode option.

When you use the MEDIUM mode option, the data distributions are obtained by sampling a percentage of data rows, using a statistical confidence level that you specify, or else a default confidence level of 95 percent. You can also specify an explicit minimum sampling size in the Resolution clause. Because the MEDIUM sample size is usually much smaller than the actual number of rows, this mode executes more quickly than the HIGH mode.

In distributions obtained by sampling, the results can vary, because different samples of rows can have different sampling errors. If the results vary significantly, you can use the Resolution clause to increase the sampling size, or to lower the percent, or to increase the confidence level to obtain more consistent results.

If the Resolution clause specifies no percent of sampled rows per bin, the default average percentage of the sample in each bin is 2.5, which divides the range into approximately 40 intervals. If you do not specify a value for confidence level, the default level is 0.95. This value can be roughly interpreted to mean that 95 times out of 100, the difference between the MEDIUM estimate and the exact value from HIGH distributions is not statistically significant.

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 MEDIUM mode distributions. For more information on the MEDIUM and HIGH mode options, see the Resolution Clause.