NO RANGE and UNKNOWN Partitions | CREATE TABLE | Teradata Vantage - Purpose and Behavior of the NO RANGE and UNKNOWN Partitions - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
Product Category
Teradata Vantage™

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 statement 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 statement 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 statement. 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 statement 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 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;

Result:

*** 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;

Result:

*** 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 a Partitioning For a Table. Also see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for additional information about the RANGE_N and CASE_N functions and how the system uses them to determine partition numbers.