15.00 - Considerations for Basing a Partitioning Expression on Updatable Current Date and Updatable Current Timestamp Expressions - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Considerations for Basing a Partitioning Expression on Updatable Current Date and Updatable Current Timestamp Expressions

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 uncompressed 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 uncompressed 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 requests.

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 requests (see “ALTER TABLE TO CURRENT” on page 140).

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 requests.

If you are not careful with the design of your partitioning expressions, then the reconciliation of rows partitioned on newly resolved DATE, CURRENT_DATE, CURRENT_TIMESTAMP, or CURRENT_DATE constants using ALTER TABLE TO CURRENT requests can be very expensive, both in terms of the time required to scan the table to locate the rows that need to be reconciled and in terms of the time required to move or delete rows that must be reconciled.

If more than a small percentage of the rows in a table or uncompressed 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 various optimizations that are available to you, as well as some of the more obvious impediments to these optimizations that you can avoid, are documented in the following topics.

  • “Rules for Reconciliation Optimization of CASE_N Partitioning Expressions Based on Updatable Current Date and Updatable Current Timestamp” on page 616
  • “Rules for Reconciliation Optimization of RANGE_N PPI Expressions Based on Updatable Current Date and Updatable Current Timestamp” on page 618
  • “Rules for Reconciliation Optimization of CASE Partitioning Expressions in Based On Updatable Current Date and Updatable Current Timestamp” on page 620
  • “Miscellaneous Reconciliation Optimizations in Partitioning Expressions Based On Updatable Current Date and Updatable Current Timestamp” on page 620
  • 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 request 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
  • See Data Dictionary for details.

  • The report produced by a SHOW request (see “SHOW request” on page 991) 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 request 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 request (see “ALTER TABLE TO CURRENT” on page 140) to ADD or DROP such ranges to a table.

  • If you create a new table using the CREATE TABLE … AS syntax, 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.