17.00 - Example: Overriding USING Options With the FOR CURRENT Option - 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 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. The 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;