16.20 - Partitioning Expressions Using DATE or TIMESTAMP Built-In Functions - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

Teradata Database 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.