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

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Partitioning Expressions Using DATE or TIMESTAMP Built‑In Functions

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

See “Considerations for Basing a Partitioning Expression on Updatable Current Date and Updatable Current Timestamp Expressions” on page 614 and the topics it references for information about how to optimize partitioning expressions based on updatable dates and updatable timestamps for reconciliation using ALTER TABLE TO CURRENT requests.

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)” on page 31).
  • 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.