Partitioning Expressions Using DATE or TIMESTAMP Built-In Functions - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

Vantage supports the BEGIN and END bound functions on Period columns in partitioning expressions anywhere that a DateTime expression is valid.

For information about how to optimize partitioning expressions based on updatable dates and updatable timestamps for reconciliation using ALTER TABLE TO CURRENT statements, see Partitioning Expression Based on Updatable Current Date and Timestamp Expressions.

Follow these guidelines for creating the partitioning expression for tables that are to be queried using DATE conditions.
  • Use RANGE_N with a DATE partitioning column. For example,
    PARTITION BY RANGE_N(date_column BETWEEN DATE '...'
                                     AND     DATE '...'
                                     EACH INTERVAL 's' t)
  • Use INTERVAL constants in the EACH clause, where t is DAY, MONTH, YEAR, or YEAR TO MONTH
  • Use DATE constants for the ranges, for example DATE '2003-08-06'.

    Do not use INTEGER or CHARACTER constants for dates.

    DATE constants are a better choice for the following reasons.
    • They are easier to read, and it is clear that the ranges are over dates.
    • They provide less dependence on the FORMAT.
  • It may seem intuitive to partition by a DATE column such as PARTITION BY salesdate.

    This does not return a syntax error; in fact, it works correctly for a few very old dates and follows the rule of implicit conversion to get an INTEGER partition number.

    The problem is that a table partitioned this way is not useful.

    Instead, use RANGE_N and EACH INTERVAL '1' DAY.

  • It might seem intuitive to specify something like PARTITION BY 7 to indicate that a date column in the primary index is to be partitioned by week, but this syntax returns a syntax error.

    Instead, use RANGE_N and EACH INTERVAL '7' DAY

  • Consider having only as many date ranges as you currently need plus a few extra for future dates. In doing so, be certain to balance the following concerns.
    • Limiting ranges to only those that are currently needed helps the Optimizer to cost plans better. It also allows for more efficient primary index access and joins if the partitioning column is not included in the primary index definition.
    • Define future ranges in such a way as to minimize the frequency of adding and dropping partitions using the ALTER TABLE statement (see ALTER TABLE (Basic Table Parameters)).

      However, if you perform your partition adding and dropping task too infrequently, you might forget to do them when they are needed to provide accurate query support.