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;