Example: Dropping a Row Range Partition When Partitioning is Defined With a RANGE_N Function and a NO RANGE Partition - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Suppose you have the following PPI base table definition.

     CREATE SET TABLE sales_table, NO FALLBACK, CHECKSUM = DEFAULT ,
                                   NO BEFORE JOURNAL, NO AFTER JOURNAL (
       product_code  CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC
                                  NOT NULL,
       sales_date    DATE FORMAT 'YYYY-MM-DD' NOT NULL NOT NULL,
       agent_id      CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC
                                  NOT NULL,
       quantity_sold INTEGER,
       product_desc  VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX (product_code, sales_date, agent_id)
     PARTITION BY RANGE_N(sales_date BETWEEN DATE '2001-01-01'
                                     AND     DATE '2003-12-31'
                                     EACH INTERVAL '1' MONTH ,
                    NO RANGE);

You then define the following PPI base table to act as the save table for an ALTER TABLE request in which you will drop one or more row partitions from sales_table.

     CREATE SET TABLE sales_table_1, NO FALLBACK,
       NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT (
        product_code  CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC
                                   NOT NULL,
        sales_date    DATE FORMAT 'YYYY-MM-DD' NOT NULL,
        agent_id      CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC,
        quantity_sold INTEGER,
        product_desc  VARCHAR(50)  CHARACTER SET LATIN NOT CASESPECIFIC
                                   NOT NULL)
     PRIMARY INDEX (product_code, sales_date, agent_id)
     PARTITION BY RANGE_N(sales_date
                          BETWEEN DATE '2001-01-01' 
                          AND     DATE '2001-12-31' 
                          EACH INTERVAL '1' MONTH , NO RANGE);

You then populate sales_table with the following rows.

     INSERT INTO sales_table 
       VALUES ('PC2',DATE '2001-01-10','AG2',5,'PC'); 
     INSERT INTO sales_table 
       VALUES ('PC3',DATE '2001-03-10','AG2',5,'PC'); 
     INSERT INTO sales_table 
       VALUES ('PC4',DATE '2002-05-10','AG2',5,'PC'); 
     INSERT INTO sales_table 
       VALUES ('PC5',DATE '2003-07-10','AG2',5,'PC'); 
     INSERT INTO sales_table 
       VALUES ('PC5',DATE '2004-07-10','AG2',5,'PC');

The following SELECT request indicates that the 5 intended rows were successfully inserted into sales_table:

     SELECT partition, product_code, sales_date, agent_id,
            quantity_sold, product_description 
     FROM sales_table 
     ORDER BY 1;
PARTITION product_code sales_date agent_id quantity_sold product_desc
--------- ------------ ---------- -------- ------------- ------------
       1 PC2          2001-01-10 AG2                  5 PC
       3 PC3          2001-03-10 AG2                  5 PC
      17 PC4          2002-05-10 AG2                  5 PC
      31 PC5          2003-07-10 AG2                  5 PC
      37 PC5          2004-07-10 AG2                  5 PC

Use an ALTER TABLE … MODIFY request to drop a range of row partitions from sales_table with the intent of saving any deleted rows in sales_table1, by specifying a WITH INSERT INTO sales_table1 clause.

     ALTER TABLE sales_table
       MODIFY 
       DROP RANGE BETWEEN DATE '2001-01-01' 
                  AND     DATE '2001-12-31' 
       WITH INSERT INTO sales_table1;
       *** Table has been modified.
       *** Total elapsed time was 1 second.

Select the contents of sales_table to check whether the rows within the row partition you dropped were deleted from the table.

     SELECT PARTITION, product_code, sales_date, agent_id,
                       quantity_sold, product_desc
     FROM sales_table 
     ORDER BY 1;
*** Query completed. 5 rows found. 6 columns returned.
*** Total elapsed time was 1 second.
PARTITION product_code sales_date agent_id quantity_sold product_desc
--------- ------------ ---------- -------- ------------- ------------
        5 PC4          2002-05-10 AG2                  5 PC
       19 PC5          2003-07-10 AG2                  5 PC
       25 PC2          2001-01-10 AG2                  5 PC<<<<< 
       25 PC3          2001-03-10 AG2                  5 PC<<<<<
       25 PC5          2004-07-10 AG2                  5 PC<<<<<

Because the base table sales_table includes a NO RANGE partition, rows are not deleted when you drop the range of row partitions. Those rows are moved to the NO RANGE partition, which is identified by partition number 25, and retained in sales_table.

To do what you had intended, you should have either defined sales_table without specifying a NO RANGE partition or deleted the rows before submitting the ALTER TABLE request.

Now assume that you create a column-partitioned version of sales_table, sales_table_cp, in another database, and also assume that you create a column-partitioned version of sales_table1, sales_table1_cp, in the same database. You submit an ALTER TABLE … MODIFY request to drop a range of partitions from sales_table_cp with the intent of saving any dropped rows in sales_table1_cp, specifying a WITH INSERT INTO sales_table1_cp clause to do so.

     ALTER TABLE sales_table_cp
       MODIFY 
       DROP RANGE BETWEEN DATE '2001-01-01' 
                  AND     DATE '2001-12-31' 
       WITH INSERT INTO sales_table1_cp;
       *** Table has been modified.
       *** Total elapsed time was 1 second.

Submit the same SELECT PARTITION request that you used for the PPI version of sales_table (except retrieve the rows from sales_table1_cp rather than sales_table1 ) to check whether the rows within the partition that was dropped were deleted from the table, and the request returns the identical result set.

     SELECT PARTITION, product_code, sales_date, agent_id,
                       quantity_sold, product_desc
     FROM sales_table_cp 
     ORDER BY 1;