Partitioning Expression Based on Updatable Current Date and Timestamp Expressions - Advanced SQL Engine - Teradata Database

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