15.10 - WITH DELETE - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Delete any row whose row partition number evaluates to null or to a value outside the valid range.

The WITH DELETE clause is sometimes referred to as a null partition handler. You cannot specify a null partition handler for a join index.

Example: Altering the Row Partitioning

This example demonstrates how using CURRENT_TIMESTAMP or CURRENT_DATE functions in a partitioning expression is most appropriate when the data must be partitioned as one or more current row partitions and one or more history row partitions, where current and history are defined with respect to the resolved CURRENT_TIMESTAMP or CURRENT_DATE function in the partitioning expression.

The example shows how you can periodically can reconcile the table to move older data from the current row partition into one or more history partitions using an ALTER TABLE TO CURRENT request rather than redefining the row partitioning for the table using explicit dates that must be determined each time you submit an ALTER TABLE DROP RANGE or ALTER TABLE ADD RANGE request. You should be take care to evaluate your intended use of CURRENT_DATE and CURRENT_TIMESTAMP functions in a partitioning expression before you define the expressions on a table or join index.

Assume that the following table definition is created in the year 2009 (the CURRENT year at the time).

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_comment       VARCHAR(79))
     PRIMARY INDEX(o_orderkey)
     PARTITION BY RANGE_N(o_orderdate BETWEEN DATE '2004-01-01'
                                      AND     DATE '2010-12-31' 
                                      EACH INTERVAL '1' MONTH)
     UNIQUE INDEX(o_orderkey);

Assume that the table is row-partitioned to record 5 years of historical data, data for the current year, and data for one future year.

If you decide to alter the row partitioning for orders next year to maintain 5 years of history data, data for the current year, and data for one future year, you can submit an ALTER TABLE request such as this in 2011:

     ALTER TABLE orders 
     MODIFY PRIMARY INDEX(o_orderkey)
     DROP RANGE WHERE PARTITION BETWEEN 1 AND 12
     ADD  RANGE BETWEEN DATE '2011-01-01' 
                AND     DATE '2011-12-31' 
                EACH INTERVAL '1' MONTH
     WITH DELETE;

For this case, you must compute the new dates and specify them explicitly in the ADD RANGE clause of the request. This requires manual intervention every year you submit the request.

Suppose that instead of creating orders as was done the first time, you create it using a CURRENT_DATE function in the partitioning expression to simplify altering its partitions. This request assumes that as of the CREATE TABLE date, the last 5 years of historical data, data for the current year, and data for one future year are to be stored in the table for a total of 7 years, which is identical to the earlier case example.

The CREATE TABLE DDL for this case looks like this.

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_comment       VARCHAR(79))
     PRIMARY INDEX (o_orderkey)
     PARTITION BY RANGE_N(o_orderdate BETWEEN 
                                       CAST(((EXTRACT(YEAR FROM                                        CURRENT_DATE)-5-1900)*10000+0101)                                        AS DATE)
                                      AND 
                                       CAST(((EXTRACT(YEAR FROM
                                       CURRENT_DATE)+1-1900)*10000+1231)
                                       AS DATE)
                                      EACH INTERVAL '1' MONTH)
     UNIQUE INDEX (o_orderkey);

You can schedule the following ALTER TABLE TO CURRENT request to be submitted annually. This request rolls the row partition window forward by efficiently dropping and adding partitions.

     ALTER TABLE orders TO CURRENT WITH DELETE;

This statement does not need to be changed each time the data needs to be repartitioned based on the new dates, as would be the case if the row partitioning expression had not been specified using a CURRENT_DATE function.

In all of the preceding cases, the row partitioning begins on a year boundary.

The first case alters the partitioning in such a way that it continues to begin on a year boundary, but could be altered to roll forward to start on some month in a year by specifying the desired dates in the ALTER TABLE request.

The second case is designed only to roll forward to start on a year boundary.

You can use the following CREATE TABLE request to roll forward to begin partitioning on the first of a month. The case assumes that, as of the CREATE TABLE date, the last 71 months of history, the current month, and 12 future months are to be stored in the table, for a total of 84 months.

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_comment       VARCHAR(79))
     PRIMARY INDEX (o_orderkey)
     PARTITION BY RANGE_N(o_orderdate BETWEEN 
                                       CAST(((EXTRACT(YEAR FROM
                                       CURRENT_DATE)-1900)*10000 +
                                       EXTRACT(MONTH FROM
                                        CURRENT_DATE)*100+01) AS DATE) -
                                        INTERVAL '71' MONTH
                                      AND 
                                       CAST(((EXTRACT(YEAR FROM
                                       CURRENT_DATE)+1-1900)*10000 +
                                       EXTRACT(MONTH FROM
                                        CURRENT_DATE)*100+01) AS DATE)+
                                        INTERVAL '13' MONTH - 
                                        INTERVAL '1' DAY
                                      EACH INTERVAL '1' MONTH)
     UNIQUE INDEX (o_orderkey);

You can schedule the following ALTER TABLE TO CURRENT request to be submitted monthly or less frequently, but at some time before you run out of future months.

The request rolls the partition window forward by efficiently dropping and adding partitions so that, as of the ALTER TABLE TO CURRENT date, the last 71 months of history data, the data for the current month, and data for 12 months in the future can be contained in the table for a total of 84 months.

     ALTER TABLE orders TO CURRENT WITH DELETE;

The following case uses simpler row partitioning, but possibly is not optimized because the entire table might need to be scanned in order to reconcile its rows when you submit an ALTER TABLE TO CURRENT request to change its row partitioning. The case assumes that, as of the CREATE TABLE date, approximately 2,191 days of history data, the data for the current day, and approximately 365 days of future data can be contained in the table, for a total of about 7 years.

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_comment       VARCHAR(79))
     PRIMARY INDEX (o_orderkey)
     PARTITION BY RANGE_N(o_orderdate BETWEEN CURRENT_DATE -
                                               INTERVAL '6' YEAR
                                      AND     CURRENT_DATE + 
                                               INTERVAL '1' YEAR
                                      EACH INTERVAL '1' MONTH)
     UNIQUE INDEX (o_orderkey);

You could schedule the following ALTER TABLE TO CURRENT request to be submitted daily or less frequently, but at some time before you run out of future days. The request rolls the partition window forward by dropping and adding row partitions only if CURRENT_DATE is the same day of the month as the last CREATE TABLE or ALTER TABLE TO CURRENT request was submitted. Otherwise, the entire table must be scanned to reconcile the rows.

     ALTER TABLE orders TO CURRENT WITH DELETE;

This request can be very inefficient if you do not submit the ALTER TABLE TO CURRENT request on the same day of the month as the last CREATE TABLE or ALTER TABLE TO CURRENT request was submitted.

Performance degrades as a function of the increase in the number of days between the last resolved date and the new resolved date because of the increasing number rows that must be moved. For example, if the last resolved date was January 1, 2010 and the next ALTER TABLE TO CURRENT request is submitted on February 2, 2010, then Teradata Database would have to move all of the orders table rows to new partitions.

Also note that using the partitioning specified for this case, the system returns an error message reporting a non-valid date error if the CREATE TABLE or ALTER TABLE TO CURRENT request is submitted on February 29.