15.00 - Purpose and Behavior of the NO RANGE and UNKNOWN Partitions - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Purpose and Behavior of the NO RANGE and UNKNOWN Partitions

The system uses the UNKNOWN partition to assign rows whose partition number cannot be evaluated because the column value on which they are partitioned evaluates to null.

When you create a partitioned table using the RANGE_N function to specify its partitioning expression, but without defining a NO RANGE partition, any row that does not evaluate to a partition numbered from 1 through 9,223,372,036,854,775,805, inclusive, when its table definition is altered using an ALTER TABLE request with a WITH INSERT [INTO] clause, is deleted from the source table and moved into the target save table specified by the WITH INSERT [INTO] clause.

When you create a partitioned table using the RANGE_N function to specify its partitioning expression with a NO RANGE partition, the rows for any dropped partition are moved into NO RANGE, even if you specify a WITH DELETE or WITH INSERT [INTO] clause in the ALTER TABLE request that drops the partition. This is because by definition the NO RANGE partition contains all rows whose partition number does not evaluate to an explicitly defined partition range or to a valid partition in the range 1 to 9,223,372,036,854,775,805, inclusive.

You can see this with a partitioned table defined with a NO RANGE partition by using a WITH INSERT [INTO] or WITH DELETE clause specified by an ALTER TABLE request. Either the data does not move into another table or it is not deleted from the specified table. Instead, it moves into the NO RANGE partition of the same table.

For example, consider the following PPI table definition.

     CREATE SET TABLE ppi_salestable, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT (
       prod_code           CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
       sales_date          DATE FORMAT 'YYYY-MM-DD',
       agent_id            CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
       quantity_sold       INTEGER,
       prod_description    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 '2001-12-31' EACH INTERVAL '1' MONTH,
                            '2002-01-01'(DATE) 
                   AND      '2002-12-31'(DATE) EACH INTERVAL '1' MONTH,
                            '2003-01-01'(DATE) 
                   AND '2003-12-31'(DATE) EACH INTERVAL '1' MONTH,
               NO RANGE);

This table has 37 partitions: 1 for each of the months in the three years it is partitioned over and 1 for NO RANGE. Note that the partition numbers for the NO RANGE and UNKNOWN partitions are assigned internally to fixed partition numbers; therefore, they are not necessarily the highest numbered partitions for any given partitioned table.

You also create the following table to be used as the target table for an ALTER TABLE request WITH INSERT [INTO] clause.

     CREATE SET TABLE ppi_salestable1, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT (
       prod_code           CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
       sales_date          DATE FORMAT 'YYYY-MM-DD',
       agent_id            CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
       quantity_sold       INTEGER,
       prod_description    VARCHAR(50) CHARACTER SET LATIN NOT                            CASESPECIFIC)
     PRIMARY INDEX (prod_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);

Now insert some rows into the source table.

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

Select all the columns from the table to verify that the rows you had intended to insert actually were inserted.

     SELECT PARTITION, prod_code, sales_date, agent_id, quantity_sold,
                       prod_description 
     FROM ppi_salestable 
     ORDER BY 1;
 
PARTITION prod_code sales_date agent_id quantity_sold prod_description
--------- --------- ---------- -------- ------------- ----------------
        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

You now drop the 12 partitions for the year 2001 with the intent of moving them into the table specified by the WITH INSERT INTO clause, ppi_salestable1.

     ALTER TABLE ppi_salestable
       MODIFY PRIMARY INDEX (product_code, sales_date, agent_id)
       DROP RANGE BETWEEN DATE '2001-01-01' 
                  AND     DATE '2001-12-31' 
                  EACH INTERVAL '1' MONTH
     WITH INSERT INTO ppi_salestable1;
 
     *** Table has been modified.
      *** Total elapsed time was 1 second.

Having dropped the set of partitions for the year 2001, you perform a quick check to ensure that the rows in those partitions were moved out of ppi_salestable and into ppi_salestable1.

     SELECT PARTITION, prod_code, sales_date, agent_id, quantity_sold, 
            prod_description
     FROM ppi_salestable 
     ORDER BY 1;
 
 *** Query completed. 5 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.
 
PARTITION prod_code sales_date agent_id quantity_sold prod_description
--------- --------- ---------- -------- ------------- ----------------
        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

Notice that the data did not move into ppi_salestable1 as intended, but instead moves into the NO RANGE partition within the same table, ppi_salestable (see the entries marked in boldface). This is because of the presence of the NO RANGE partition in ppi_salestable. Because of that specification, any rows in partitions deleted from ppi_salestable or whose partition number evaluates to a value outside the inclusive range 1- 9,223,372,036,854,775,805 are moved into the NO RANGE partition whether you define a WITH INSERT [INTO] or WITH DELETE clause or not. Therefore, if you want to use the functionality provided by these clauses, no not specify a NO RANGE partition for the source table.

See “Rules For Altering the Partitioning Expression For a Table” on page 110. Also see SQL Functions, Operators, Expressions, and Predicates for additional information about the RANGE_N and CASE_N functions and how the system uses them to determine partition numbers.