Example: Overriding USING Options With the FOR CURRENT Option - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;