15.10 - Example: Overriding USING Options With the FOR CURRENT Option - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

The FOR CURRENT option is useful for overriding the existing USING options for the current request, but to continue to use those options for future requests.

For example, if a table has massive updates or deletes, and you want to force a recollection of statistics for the current request without honoring the THRESHOLD options currently in effect, specify the NO THRESHOLD FOR CURRENT option to force statistics to be recollected. Subsequent recollections are still subject to the previous THRESHOLD option.

The following examples illustrate this functionality.

The first request uses a user-specified sample percentage of SAMPLE 75 PERCENT for the current recollection of statistics on orders.o_orderkey. Teradata Database remembers the existing USING options and uses them for subsequent recollections.

     COLLECT STATISTICS
     USING SAMPLE 75 PERCENT FOR CURRENT
     COLUMN o_orderkey
     ON orders;

The next request forces statistics to be recollected on orders.o_orderdatetime by ignoring the existing threshold options.

The existing USING options continue to be used for subsequent recollections on orders. o_orderdatetime.

     COLLECT STATISTICS
     USING NO THRESHOLD FOR CURRENT
     COLUMN o_orderdatetime
     ON orders;