17.00 - Example: Collecting Sampled Statistics with USING Options - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

The following statements sample statistics on column o_orderkey with a user-specified sampling percentage.

The following statement collects statistics by reading a sample of 10% of the table rows.

     COLLECT STATISTICS USING SAMPLE 10 PERCENT
     COLUMN o_orderkey
     ON orders;

The following statement samples statistics on the o_orderdatetime column using a system-selected sampling percentage. The system collects full statistics and remembers the system-selected sampling percentage.

The system determines whether to downgrade the sampling percentage after you have recollected those statistics a few times.

     COLLECT STATISTICS USING SYSTEM SAMPLE
     COLUMN o_orderdatetime
     ON orders;

The following statement recollects the statistics on orders.o_orderkey using a sampling size of 10% and recollects statistics on orders.o_orderdatetime using a system-selected sampling size because the Optimizer remembers those specifications from previous specifications for collecting statistics on orders.

     COLLECT STATISTICS ON orders;

The following statement is equivalent to the previous statement but because you explicitly specify the column references, the system recollects the statistics on o_orderkey using the stored sampling size of 10 percent and on o_orderdatetime using the stored system-selected sample size.

     COLLECT STATISTICS 
     COLUMN o_orderkey,
     COLUMN o_orderdatetime
     ON orders;

The following statement changes the existing sample clause from a user-specified sample of 10 percent to a system-selected sample percentage and then recollects the statistics.

     COLLECT STATISTICS USING SYSTEM SAMPLE 
     COLUMN o_orderkey
     ON orders;