15.00 - Partitioning Expressions for Temporal Tables - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Partitioning Expressions for Temporal Tables

To improve the performance of current queries on a temporal table, the table should be row partitioned:

  • For tables with valid-time columns, the partitions logically separate the current and future rows from the history rows, so fewer rows need to be scanned for current queries.
  • For tables with transaction-time columns, the partitions logically separate the open rows from the closed rows.
  • For bitemporal tables, the partitions separate open current and future rows from open history rows from all closed rows.
  • Note: Like other types of tables, temporal tables can have several levels of partitioning defined, including column partitioning. The temporal row partitioning described here should be one of those levels. For more information on column partitioning, see Database Design and SQL Data Definition Language.

    The recommended partitioning expressions discussed for each table type below reference the built-in functions CURRENT_DATE, CURRENT_TIMESTAMP, or both.

    As time passes, the values of CURRENT_DATE and CURRENT_TIMESTAMP differ from the values that were used to resolve CURRENT_DATE and CURRENT_TIMESTAMP in the partitioning expressions when the table was created. Because of this, the partition intended to hold the current and future rows might include rows that are not strictly current. The optimizer can nevertheless successfully find current rows within the partition. The presence of some history rows in the current partition will not adversely affect performance unless there are a great many history rows there.

    Use the ALTER TABLE TO CURRENT statement periodically to move history rows out of the current partition into the history partition. ALTER TABLE TO CURRENT resolves the partitioning expressions again, transitioning rows to their appropriate partitions per the updated partitioning expressions. For more information on ALTER TABLE TO CURRENT, see SQL Data Definition Language.

    Note: To ensure optimal row partition elimination, the granularity of the time specification in AS OF and SEQUENCED queries on row partitioned temporal tables should be no finer than the valid-time column in the query with the coarsest time granularity.

    Row Partitioning Valid-Time Tables

    The following table describes the recommended partitioning expressions for a valid-time table, where vtcolumn represents the valid-time column.

     

    Valid-Time Column Data Type

    Recommended Partitioning Expressions

    PERIOD(DATE)
    NOT NULL

     

    PARTITION BY CASE_N(
     END(
    vtcolumn) >= CURRENT_DATE AT '-12:59',
     NO CASE)

    (where AT '-12:59' is a shorthand form of AT INTERVAL -'12:59' HOUR TO MINUTE)

    Note: If the valid-time column is a derived period column, the component columns must be defined as NOT NULL, and the derived period column is considered to be defined as NOT NULL.

    PERIOD(DATE)

    PARTITION BY CASE_N(
     END(
    vtcolumn) IS NULL OR 
     END(vtcolumn) >= CURRENT_DATE AT '-12:59',
     NO CASE)

    (where AT '-12:59' is a shorthand form of AT INTERVAL -'12:59' HOUR TO MINUTE)

    PERIOD(TIMESTAMP[(n)]
    [WITH TIME ZONE])
    NOT NULL

    PARTITION BY CASE_N(
     END(
    vtcolumn) >= CURRENT_TIMESTAMP,
     NO CASE)

    PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE])

    PARTITION BY CASE_N(
     END(
    vtcolumn) IS NULL OR
     END(vtcolumn) >= CURRENT_TIMESTAMP,
     NO CASE)

    By using the recommended physical partitioning for a valid-time table, the physical partitions are as follows:

  • The current partition has rows that are or were valid as of the last resolved CURRENT_DATE or CURRENT_TIMESTAMP value for the partitioning expression and rows that are in the future with respect to that date or timestamp.
  • Note that rows with a valid-time column value as NULL are in the current partition (but are not considered current or valid rows).

  • The history partition has rows that were no longer valid as of the last resolved CURRENT_DATE or CURRENT_TIMESTAMP value for the partitioning expression.
  • Note: Most of the AS OF queries are concerned with times that have already past. Consequently, these queries will not benefit from the recommended partitioning expressions described above. If AS OF queries are expected to be frequent, one way to get the benefit of row partition elimination is to partition on END(<VT_column>) and, within each partition, by BEGIN(<VT_column>) such that there is a 20% distribution within each outer partition.

    Row Partitioning Transaction-Time Tables

    The following partitioning expression is recommended for a transaction-time table, where ttcolumn represents the transaction-time column.

       PARTITION BY CASE_N (END(ttcolumn) >= CURRENT_TIMESTAMP, NO CASE)

    By using the recommended physical partitioning for a transaction-time table, the physical partitions are as follows:

    1 The current partition has rows that are or were open as of the last resolved CURRENT_TIMESTAMP value for the partitioning expression.

    2 The history partition has rows that were closed as of the last resolved CURRENT_TIMESTAMP value for the partitioning expression.

    Row Partitioning Bitemporal Tables

    The following table describes the recommended partitioning expressions for a bitemporal table, where vtcolumn represents the valid-time column and ttcolumn represents the transaction-time column.

     

    Valid-Time Column Data Type

    Recommended Partitioning Expressions

    PERIOD(DATE)
    NOT NULL

    PARTITION BY CASE_N(
     END(
    vtcolumn) >= CURRENT_DATE AT '-12:59'
      AND END(ttcolumn) >= CURRENT_TIMESTAMP,
     END(vtcolumn) < CURRENT_DATE AT '-12:59'
      AND END(ttcolumn) >= CURRENT_TIMESTAMP,
     END(ttcolumn) < CURRENT_TIMESTAMP)

    (where AT '-12:59' is a shorthand form of AT INTERVAL -'12:59' HOUR TO MINUTE)

    (The END(ttcolumn) < CURRENT_TIMESTAMP expression represents closed rows, and is used instead of NO CASE to achieve better partition elimination.)

    PERIOD(DATE)

    PARTITION BY CASE_N(
     (END(
    vtcolumn) IS NULL OR
      END(vtcolumn) >= CURRENT_DATE AT '-12:59')
      AND END(ttcolumn) >= CURRENT_TIMESTAMP,
     END(vtcolumn) < CURRENT_DATE AT '-12:59'
      AND END(ttcolumn) >= CURRENT_TIMESTAMP,
     END(ttcolumn) < CURRENT_TIMESTAMP)

    (where AT '-12:59' is a shorthand form of AT INTERVAL -'12:59' HOUR TO MINUTE)

    PERIOD(TIMESTAMP[(n)]
    [WITH TIME ZONE])
    NOT NULL

    PARTITION BY CASE_N(
     END(
    vtcolumn) >= CURRENT_TIMESTAMP AND
     END(ttcolumn) >= CURRENT_TIMESTAMP,
     END(vtcolumn) < CURRENT_TIMESTAMP AND
     END(ttcolumn) >= CURRENT_TIMESTAMP,
     END(ttcolumn) < CURRENT_TIMESTAMP)

    PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE])

    PARTITION BY CASE_N(
    (END(vtcolumn) IS NULL OR END(vtcolumn) >= CURRENT_TIMESTAMP) AND END(ttcolumn) >= CURRENT_TIMESTAMP, 
    END(vtcolumn) < CURRENT_TIMESTAMP AND END(ttcolumn) >= CURRENT_TIMESTAMP,
    END(ttcolumn) < CURRENT_TIMESTAMP)

    By using the recommended row partitioning for a bitemporal table, the partitions are as follows:

  • The current partition has rows that are or were valid and open as of the last resolved CURRENT_DATE or CURRENT_TIMESTAMP value for the partitioning expression.
  • The valid-time history/transaction-time open partition has rows that are or were no longer valid but were still open as of the last resolved CURRENT_DATE or CURRENT_TIMESTAMP value for the partitioning expression.
  • The transaction-time history partition has rows that were closed as of the last resolved CURRENT_DATE or CURRENT_TIMESTAMP value for the partitioning expression.
  • Related Information

     

    For more information on...

    See...

    CREATE TABLE (temporal form)

    “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65

    ALTER TABLE TO CURRENT (regular form)

    SQL Data Definition Language

    Multilevel partitioned primary indexes

  • CREATE TABLE in SQL Data Definition Language
  • Database Design