The system uses the UNKNOWN partition to assign rows whose partition number cannot be evaluated because the column value on which the rows 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.
To see this with a partitioned table defined with a NO RANGE partition, use a WITH INSERT [INTO] or WITH DELETE clause specified by an ALTER TABLE statement. Either the data does not move into another table or the data is not deleted from the specified table. Instead, the data moves into the NO RANGE partition of the same table.
For example, consider the following PPI table definition (ppi_salestable is on the Block File System.):
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 month in the three years the table is partitioned over and 1 for NO RANGE. The partition numbers for the NO RANGE and UNKNOWN partitions are assigned internally to fixed partition numbers. Therefore, the partition numbers 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);
Insert 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 columns from the table to verify the rows 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
Drop the 12 partitions for the year 2001, intending to move those partitions 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.
Check that the rows in the removed 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
The data did not move into ppi_salestable1 as intended. Instead, the data moved into the NO RANGE partition within the same table, ppi_salestable. Because of the NO RANGE partition in ppi_salestable, 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 moved into the NO RANGE partition, whether or not you define a WITH INSERT [INTO] or WITH DELETE clause. If you want to use these clauses, do not specify a NO RANGE partition for the source table.