15.10 - table_name - 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

Name of the table whose row partitioning is to be reconciled to a new current date or timestamp value.

Example: Altering the Row Partitioning of a Table When the Partitioning Expression Is Based on Multiple CURRENT_DATE Functions

Assume that the table is partitioned with the latest 2 quarters in separate row partitions and all other data in another row partition. The example below reconciles CURRENT_DATE at the beginning of each quarter. Assume that the table is created on January 1, 2009; therefore, CURRENT_DATE is resolved to January 1, 2009 at creation time.

     CREATE SET TABLE sales, NO FALLBACK (
       storeID   INTEGER,
       amount    DECIMAL(10,2),
       sale_date DATE FORMAT 'YYYY/MM/DD' NOT NULL)
     PRIMARY INDEX (storeID) 
     PARTITION BY CASE_N(sale_date>=CURRENT_DATE/*latest quarter data*/,
                         sale_date<CURRENT_DATE 
                     AND sale_date>=CURRENT_DATE-INTERVAL '3' MONTH,
                         NO CASE);

The table contains the following rows with a resolved CURRENT_DATE of January 1, 2009:

sales      
StoreID Amount Sale_Date PARTITION
1 1000.00 2009-01-31 1
1 2000.00 2009-01-01 1
1 3500.00 2009-01-15 1
1 500.00 2008-09-15 2
1 2000.00 2008-12-15 2
1 5000.00 2009-04-01 3

Assume that you submit the following request on April 1, 2009:

     ALTER TABLE sales TO CURRENT; 

The resolved CURRENT_DATE is changed to April 1, 2009 and the rows in the table are reconciled as follows.

sales      
StoreID Amount Sale_Date PARTITION
1 1000.00 2009-01-31 2
1 2000.00 2009-01-01 2
1 3500.00 2009-01-15 2
1 500.00 2008-09-15 3
1 2000.00 2008-12-15 3
1 5000.00 2009-04-01 3

Suppose you submit the following CREATE TABLE request on January 1, 2009.

     CREATE SET TABLE customer, NO FALLBACK (
       cust_name              CHARACTER(10),
       cust_no                INTEGER,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD' NOT NULL)
     PRIMARY INDEX (cust_no) 
     PARTITION BY CASE_N(policy_expiration_date>=CURRENT_DATE,
                         policy_expiration_date<CURRENT_DATE 
                     AND policy_expiration_date>=CURRENT_DATE-
                                                 INTERVAL '3' MONTH);

The rows in the table are as follows, with the resolved CURRENT_DATE as January 1, 2009.

customer      
cust_name cust_no policy_expiration_date PARTITION
Li 1 2009-01-31 1
Khan 2 2009-01-01 1
Reddy 3 2009-01-15 1
Smith 5 2008-12-15 2

You submit the following ALTER TABLE TO CURRENT request on April 1, 2009.

     ALTER TABLE customer TO CURRENT; 

Because the following row cannot be moved to any row partition based on the revised CURRENT_DATE value, the system returns an error message to the requestor.

customer      
cust_name cust_no policy_expiration_date PARTITION
Smith 5 2008-12-15 2

To avoid this error, you instead submit the following ALTER TABLE TO CURRENT request on April 1, 2009:

     ALTER TABLE customer TO CURRENT WITH DELETE; 

As a result of this ALTER TABLE TO CURRENT request, Teradata Database deletes the following row from the table because it is no longer of interest.

customer      
cust_name cust_no policy_expiration_date PARTITION
Smith 5 2008-12-15 2

The rows in the table are then the following, with the resolved CURRENT_DATE as April 1, 2009:

customer      
cust_name cust_no policy_expiration_date PARTITION
Li 1 2009-01-31 2
Khan 2 2009-01-01 2
Reddy 3 2009-01-15 2

Example: Non-optimized Use of a CURRENT_DATE Function in a CREATE TABLE Request

This example specifies the CURRENT_DATE function in a CASE_N condition in a way that reconciliation using an ALTER TABLE TO CURRENT request cannot be optimized to scan fewer than all of the partitions. As a result, each time you submit an ALTER TABLE TO CURRENT request on customer, Teradata Database must perform a full-table scan to reconcile the row partitioning for the newly resolved date.

     CREATE SET TABLE customer, NO FALLBACK (
       cust_name              CHARACTER(10),
       cust_no                INTEGER,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX(cust_no) 
     PARTITION BY CASE_N(policy_expiration_date=CURRENT_DATE, 
                  NO CASE);

Example: Non-optimized Use of CURRENT_DATE Functions in CREATE TABLE Requests

This example also specifies the CURRENT_DATE function as an argument for CASE_N conditions in a way that reconciliation using an ALTER TABLE TO CURRENT request cannot be optimized to scan fewer than all of the row partitions of customer.

The request specifies a CURRENT_DATE function for two different partitioning columns, so Teradata Database cannot determine when all rows in certain row partitions are not impacted because there is no known relationship between the policy_expiration_date and birth_date columns that can be used to determine the impacts of a newly resolved date. As a result, each time you submit an ALTER TABLE TO CURRENT request on customer, Teradata Database must perform a full-table scan to reconcile the row partitioning for the new resolution date.

     CREATE SET TABLE customer, NO FALLBACK (
       cust_name              CHARACTER(10),
       cust_no                INTEGER,
       birth_date             DATE,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX (cust_no) 
     PARTITION BY CASE_N(policy_expiration_date >= CURRENT_DATE,
                         policy_expiration_date >= CURRENT_DATE,
                         birth_date < CURRENT_DATE - INTERVAL '20' YEAR, 
                         NO CASE);