Example: Recollecting Statistics When Statistics Were First Collected Specifying a USING Clause - Advanced SQL Engine - Teradata Database

USING clause option settings are retained for each column, index, or multicolumn set on which statistics are collected and applied when statistics are recollected.

To continue to use the same USING options, do not specify USING options when you recollect statistics. Specifying USING options at recollection time resets the previous options and starts over with new options you specify, if different from the previous options.

If the Optimizer decides to skip the recollection for a request with new USING options, the DBC.StatsTbl dictionary row is updated to remember these new options without recollecting the statistics. The column DBC.StatsTbl.LastAlterTimeStamp is updated to reflect the change of options, but DBC.StatsTbl.LastCollectTimeStamp remains the same and reflects the last statistics collection timestamp.

The following requests collect the statistics on o_orderkey and o_orderdatetime for the first time. The USING options are retained for each column, index, or multicolumn set on which statistics are collected.

     COLLECT STATISTICS
     USING SYSTEM SAMPLE AND SYSTEM THRESHOLD
     COLUMN o_orderkey
     ON orders;
     COLLECT STATISTICS
     USING SYSTEM THRESHOLD
     COLUMN o_orderdatetime
     ON orders;

The next table-level example recollects the statistics with the saved USING options for o_orderkey and o_orderdatetime.

     COLLECT STATISTICS ON orders;

The following request recollects the statistics with the saved USING options for o_orderkey and o_orderdatetime. This is equivalent to the table-level COLLECT STATISTICS request shown in the earlier example.

     COLLECT STATISTICS COLUMN o_orderkey,
                        COLUMN o_orderdatetime
     ON orders;

The next example resets the using option from system-determined sample to a user-specified sample of 10 percent for o_orderkey and recollects the statistics

Note that the system-determined THRESHOLD option is still in effect.

If the THRESHOLD option decides to skip the statistics recollection, the dictionary row for this recollection is updated with the new sample percentage without recollecting the statistics.

     COLLECT STATISTICS
     USING SAMPLE 10 PERCENT
     COLUMN o_orderkey
     ON orders;

The next example resets the USING option from system-determined threshold to a new user-specified threshold of 7 days on o_orderdatetime and recollects the statistics.

If the new THRESHOLD option decides to skip the recollection, the dictionary row for this recollection is updated with the new THRESHOLD option without recollecting statistics.

     COLLECT STATISTICS
     USING THRESHOLD 7 DAYS
     COLUMN o_orderdatetime
     ON orders;