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