16.20 - Partitioning Expression Based on Updatable Current Date and Timestamp Expressions - 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)

When the logic of a significant number of your DML requests against a table typically specify DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions, you should consider taking advantage of the functionality provided by updatable current dates and updatable current timestamps to partition the rows of the tables you most frequently access with such queries.

A partitioned primary index enables Teradata Database to partition the rows of a table or noncompressed join index in such a way that row subsets can be accessed efficiently without resorting to full-table scans. If the partitioning expression is defined using an updatable current date or updatable current timestamp, the partition that contains the most recent rows can be defined to be as narrow as possible to optimize efficient access to those rows. An additional benefit of an updatable current date or updatable current timestamp for a partitioning is that the partitioning expression can be designed in such a way that it might not need to be changed as a function of time.

To do this, you can specify the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions in the partitioning expression of a table or noncompressed join index and then periodically update the resolution of their values. This enables rows to be repartitioned on the newly resolved values of the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions at any time you determine that they require reconciliation. You can update the resolution of your partitioning scheme by submitting appropriate ALTER TABLE TO CURRENT statements.

To realize the optimal benefit from partitioning a table or join index using updatable current date or updatable current timestamp values, you must periodically reconcile their partitioning to a more recent date or timestamp value using ALTER TABLE TO CURRENT statements. See ALTER TABLE TO CURRENT.

Although you can specify the DATE, CURRENT_DATE, and CURRENT_TIMESTAMP functions anywhere in a partitioning expression where a date or timestamp constant is valid, you should take special care to optimize the definitions of your partitions in such a way that they can be reconciled optimally using ALTER TABLE TO CURRENT statements.

Use care in designing your partitioning expressions to minimize the expense of reconciling the rows partitioned on newly resolved DATE, CURRENT_DATE, CURRENT_TIMESTAMP, or CURRENT_DATE constants using ALTER TABLE TO CURRENT statements. Reconciling the row can be expensive in terms of the time required to scan the table to locate the rows that need to be reconciled and the time required to move or delete rows.

If more than a small percentage of the rows in a table or noncompressed join index must be reconciled without being able to take advantage of the optimizations that are available to you, some other form of partitioning might be more advantageous for your applications. An example of such an optimization is full partition deletion of rows.

The following set of rules applies to using the DATE, CURRENT_DATE, and CURRENT_TIMESTAMP functions to define a partitioning expression for a partitioned primary index.
  • You can specify the DATE and CURRENT_DATE functions anywhere in a partitioning expression that a date is valid.

    You can specify a CURRENT_TIMESTAMP function anywhere in a partitioning expression that a timestamp is valid.

    However, note that the result of a test value must not have the TIMESTAMP data type in a RANGE_N function.

  • The report produced by a SHOW TABLE or SHOW JOIN INDEX statement returns the user-specified partitioning expression containing the user-specified expression with DATE, CURRENT_DATE, or CURRENT_TIMESTAMP and not the resolved date or timestamp value.
    You can retrieve the last resolved DATE or CURRENT_DATE value, CURRENT_TIMESTAMP value, or both using one of the following system views.
    • ResolvedDTSV
    • ResolvedDTSVX

      For more information, see Teradata Vantage™ Data Dictionary, B035-1092.

  • The report produced by a SHOW request (see SHOW request) returns the user-specified partitioning expression.

    The report produced by a SHOW QUALIFIED request returns the last resolved date and timestamp for the DATE, CURRENT_DATE, and CURRENT_TIMESTAMP specified in the partitioning expression.

  • The following statements are true for partitioning expressions that specify a RANGE_N function with a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function.
    If a partitioning expression is … THEN you cannot use an ALTER TABLE statement to …
    single-level ADD or DROP ranges to the partitioning expression.
    multilevel ADD or DROP ranges to the level of the partitioning expression.

    Instead, you must use an ALTER TABLE TO CURRENT statement to ADD or DROP such ranges from a table. See ALTER TABLE TO CURRENT.

  • If you create a new table using the CREATE TABLE … AS statement, the following rules apply.
    • If the source table has a partitioning defined using the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions, and the new table is a copy of the source table and its index definitions, then Teradata Database copies the newly created table with the resolved date and timestamp from the source table.
    • In all other cases, Teradata Database resolves any DATE, CURRENT_DATE, or CURRENT_TIMESTAMP values in the partitioning definition of the target table to the creation date, creation timestamp, or both, for the new table.