Working with the ALTER TABLE TO CURRENT Request | Teradata Vantage - General Usage Guidelines and Rules for ALTER TABLE TO CURRENT Requests - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

Although you can specify the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions anywhere in a partitioning expression where a date or timestamp constant is valid, you should exercise great care in how you use them.

If you specify multiple ranges using a DATE or CURRENT_DATE function in one of the ranges, and then later reconcile the partitioning the range specified using CURRENT_DATE might overlap one of the existing ranges. If so, reconciliation returns an error to the requestor. If this happens, you must recreate the table with a new partitioning expression based on DATE or CURRENT_DATE. Because of this, you should design a partitioning expression that uses a DATE or CURRENT_DATE function in one of its ranges with care.

DATE, CURRENT_DATE, and CURRENT_TIMESTAMP functions in a partitioning expression are most appropriate when the data must be partitioned as one or more Current partitions and one or more History partitions, where the terms Current and History are defined with respect to the resolved DATE, CURRENT_DATE, or CURRENT_TIMESTAMP values in the partitioning expression.

This enables you to reconcile a table or join index periodically to move older data from the current partition into one or more history partitions using an ALTER TABLE TO CURRENT request instead of redefining the partitioning using explicit dates that must be determined each time you alter a table using ALTER TABLE requests to ADD or DROP ranges. You should evaluate how a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function will require reconciliation in a partitioning expression before you define such expressions on a table or join index.

For a join index, reconciliation includes its WHERE clause if its predicate specifies a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function. Again, the actual current date or current timestamp value is always earlier than the resolved DATE, CURRENT_DATE, or CURRENT_TIMESTAMP in the partitioning expression, and it is reconciled based on the newly resolved value after the ALTER TABLE TO CURRENT request completes.

Be aware that using an ALTER TABLE TO CURRENT request to reconcile rows with newly resolved DATE, CURRENT_DATE, or CURRENT_TIMESTAMP values in their partitioning expressions can be expensive, both because of the time required to scan a table or join index to find rows that need to be reconciled and because of the time required to move or delete rows that need to be reconciled if you specify a WITH DELETE or WITH INSERT clause with the ALTER TABLE TO CURRENT request for a table. You cannot specify a null partition handler for an ALTER TABLE TO CURRENT request made on a join index.

If more than a small percentage of table rows need to be reconciled without optimizations, such as whole partition deletions, another form of partitioning might be more appropriate.

When you build your ALTER TABLE TO CURRENT requests, you should specify modified functions in your partitioning expressions such as DATE - INTERVAL '2' DAY, CURRENT_DATE - INTERVAL '2' DAY, or some other appropriate adjustment rather than specifying an unmodified DATE or CURRENT_DATE function because requests might be submitted in different time zones than the session time zone in which the ALTER TABLE TO CURRENT request is submitted. Such adjustments ensure that the Optimizer query plans remain the same for the same request regardless of the session time zone.

Specifying a partitioning expression that uses a CURRENT_TIMESTAMP function avoids the time zone issues that occur with partitioning expressions that use a DATE or CURRENT_DATE function, and these partitioning expressions do not require any adjustments to be universally applicable.

The rules for ALTER TABLE TO CURRENT requests are identical for row partitioning and column partitioning; however, there can be a negative performance impact if the table or join index has column partitioning in addition to row partitioning because it is often costly to move a row from one row partition to another.

The following general rules apply to the ALTER TABLE TO CURRENT statement.
  • A table or join index that is partitioned using a DATE or CURRENT_DATE function, a CURRENT_TIMESTAMP function, or both in the partitioning definition can be reconciled to a newly resolved date or timestamp using an ALTER TABLE TO CURRENT request, which resolves the values of DATE, CURRENT_DATE, and CURRENT_TIMESTAMP based on their current values and reconciles the partitioning accordingly.
  • If you submit an ALTER TABLE TO CURRENT request and the partitioning on the specified table or join index does not specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function, the database returns an error to the requestor.

    A join index can also specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function in its WHERE clause. In that case, the database neither aborts the request nor does it return an error to the requestor.

    An error should only occur if neither the partitioning expression nor the WHERE clause of a join index definition specifies a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function.

  • When you submit an ALTER TABLE TO CURRENT request, the database evaluates the partitioning expressions using the newly resolved current date or current timestamp value. The rows in the table or join index being reconciled are scanned and adjusted by moving rows into new partitions or by deleting rows if they are no longer associated with a partition as a result of the ALTER TABLE TO CURRENT operation.
    • If you specify a null partition handler for a join index, the database returns an error to the requestor. This happens because you cannot delete rows from a join index using this statement.
    • If you do not specify a null partition handler and there are rows that are invalidated by the newly reconciled partitioning expression, the database returns an error to the requestor.

      Because you cannot specify a null partition handler for a join index, partitioning defined on the index using DATE, CURRENT_DATE, or CURRENT_TIMESTAMP must be such that an ALTER TABLE TO CURRENT request does not cause this error. Otherwise, the join index may not reconcile to a new date or timestamp value.

      In this case, you must drop the existing join index and then create a new index with an appropriate partitioning definition.

  • The database updates all relevant secondary, join, and hash indexes as needed if table rows of the table are moved or deleted during reconciliation.
  • If you specify either a WITH DELETE clause or a WITH INSERT clause, you must disable any delete triggers on table_name.

    You must also disable any insert triggers on table_name if you specify a WITH INSERT clause. If you do not disable such insert triggers, then the database returns an error to the requestor.

    You must remember to re-enable any such disabled triggers after you have successfully submitted your ALTER TABLE TO CURRENT request.

  • If a newly resolved date or timestamp value evaluates to an earlier date or timestamp than the previously reconciled date or timestamp, the database returns an error to the requestor.

    While it is possible for such dates or timestamps to occur because of clock drifts, adjustments to the system clock, or session time differences for a date, reconciliation should occur infrequently enough that a clock reset or adjustment does not cause the regression of current date or timestamp values.

    The projected usage of reconciliation is based on the assumption that date and timestamp values always progress, so such date and timestamp resolution problems are not expected to occur.

  • The latest revolved date and timestamp for a base table or join index is maintained in table DBC.TVM.
  • If a newly resolved date evaluates the starting expression (containing the DATE or CURRENT_DATE function) of a RANGE_N function to a partition boundary, the database drops all of the partitions that are earlier than this partition. Otherwise, the database repartitions the entire table using the new partitioning expression.

    For example, consider the following CREATE TABLE request submitted on April 1, 2006.

         CREATE TABLE ppi (
           i INTEGER,
           j DATE)
         PRIMARY INDEX(i) 
         PARTITION BY RANGE_N(j BETWEEN CURRENT_DATE
                                AND     CURRENT_DATE+INTERVAL'1' YEAR -                                                 INTERVAL'1' DAY
                                EACH INTERVAL'1' MONTH);

    In this example, consider the last resolved date to be April 1, 2006 and assume that, when the value for DATE or CURRENT_DATE is DATE '2006-06-01', you submit an ALTER TABLE TO CURRENT request. The starting expression with the newly resolved DATE or CURRENT_DATE value falls on a partition boundary of the third partition; therefore, the database drops partitions 1 and 2, and the last reconciled date is set to the newly resolved value for DATE or CURRENT_DATE.

    Now suppose you submit an ALTER TABLE TO CURRENT request on DATE '2006-06-10'. The starting expression with the newly resolved DATE or CURRENT_DATE value does not fall on a partition boundary, so the database scans all of the rows, and repartitions them based on the new partitioning expression. The partition boundary after this request aligns to the tenth day of a month instead of the earlier first day of a month.

  • With an updatable DATE or CURRENT_DATE value in a partitioning expression, it becomes possible for a partitioning expression based on a RANGE_N function to become obsolete after some time passes. Exercise great caution specifying RANGE_N functions for such cases, only doing so after you fully understand its implications for reconciliation and its applicability as the value changes each time you reconcile the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP value.

    For example, consider the following CREATE TABLE request.

         CREATE TABLE ppi (
           i INTEGER,
           j DATE)
         PRIMARY INDEX(i)
         PARTITION BY RANGE_N(j BETWEEN CURRENT_DATE
                                AND     DATE '2008-01-01'
                                EACH INTERVAL '1' MONTH);

    If you reconcile this table using an ALTER TABLE TO CURRENT request after January 1, 2008, the request aborts and the database returns an error to the requestor because all the defined ranges are null.

    Although you can specify the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions anywhere in a partitioning expression that a date or timestamp constant is valid, you must take appropriate caution in doing so.

    For example, reconciliation of rows with a newly resolved DATE, CURRENT_DATE, or CURRENT_TIMESTAMP value in a partitioning expression using an ALTER TABLE TO CURRENT request might be expensive both from the time required to scan the table to determine which rows need to be reconciled and because of the time to required to move or delete rows that require reconciliation.

    If more than a small percentage of the rows in a table must be reconciled without the benefit of optimizations such as whole partition delete operations, some other form of partitioning might be more appropriate.

    The cases provided in the next several topics demonstrate how reconciliation can be expensive and how it can sometimes be optimized.