General Usage Guidelines and Rules for ALTER TABLE TO CURRENT Requests
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 aborts the request and 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 join index can also specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function in its WHERE clause. In that case, Teradata 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.
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 might never be reconciled to a new date or timestamp value.
To handle this problem, you must drop the existing join index and then create a new index with an appropriate partitioning definition.
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 Teradata Database aborts the request and 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.
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.
For example, consider the following CREATE TABLE request submitted on April 1, 2006.
CREATE TABLE ppi (
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, Teradata 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 Teradata 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.
For example, consider the following CREATE TABLE request.
CREATE TABLE ppi (
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 Teradata 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.