Example: Collecting Statistics on Built-In Function Expressions - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

You can also collect statistics on constant expressions that refer to built-in functions.

The Optimizer remembers the materialized values for the CURRENT_TIMESTAMP, CURRENT_DATE and USER functions when statistics are collected and then substitutes the stored materialized values when the statistics are used.

For other built-in functions like SESSION and CURRENT_ROLE, the Optimizer substitutes the values based on the session parameters at the time the statistics are used. Even though the database allows you to specify any built-in functions, you should restrict your collecting of statistics to built-in functions other than CURRENT_TIMESTAMP, CURRENT_DATE and USER in the expressions.

In the following request, the Optimizer substitutes the value for CURRENT_DATE for the CASE expression and then remembers it. When the statistics are used for this expression, the Optimizer substitutes the saved value for CURRENT_DATE.

     COLLECT STATISTICS 
     COLUMN CASE WHEN CAST(o_orderdatetime AS DATE AT 0) = CURRENT_DATE 
                 THEN 'orders-today'
                 ELSE 'orders-old'
            END AS stats_orders_today_and_old
     ON orders;