Assume you have created the following table:
CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHAR(1) NOT CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_orderpriority CHAR(21), o_comment VARCHAR(79)) PRIMARY INDEX (o_orderkey) PARTITION BY (RANGE_N(o_custkey BETWEEN 0 AND 49999 EACH 1000), RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH) ADD 24, RANGE_N(o_orderpriority BETWEEN 'high' AND 'highest', 'low' AND 'lowest', 'medium' AND 'medium', NO RANGE OR UNKNOWN)) UNIQUE INDEX (o_orderkey);
The following ALTER TABLE request is valid only if orders is empty:
ALTER TABLE orders MODIFY PARTITION BY (RANGE_N(o_custkey BETWEEN 0 AND 49999 EACH 1000), RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH ), CASE_N(o_orderstatus = 'S', o_orderstatus = 'C', o_orderstatus = 'F', o_orderstatus = 'T', NO CASE OR UNKNOWN) );
Assuming the preceding table definition for orders , you can submit any one of the following equivalent requests if there are no rows with an o_orderdate value between 2000-01-01 and 2000-12-31 to alter the row partitioning expression for level 2:
ALTER TABLE orders MODIFY DROP RANGE#L2 BETWEEN DATE '2000-01-01' AND DATE '2000-12-31' ADD RANGE BETWEEN DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH; ALTER TABLE orders MODIFY DROP RANGE#L2 WHERE orders.PARTITION#L2 BETWEEN 1 AND 12 ADD RANGE#L2 BETWEEN DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH; ALTER TABLE orders MODIFY DROP RANGE#L2 WHERE PARTITION#L2 IN (1,2,3,4,5,6,7,8,9,10,11,12) ADD RANGE BETWEEN DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH;
Assuming the table definition for orders , the following ALTER TABLE request is valid even if the table is populated with rows. However, the range being dropped must be empty because the request does not specify a WITH clause.
ALTER TABLE orders MODIFY PRIMARY INDEX DROP RANGE#L1 BETWEEN 0 AND 999 ADD RANGE BETWEEN 50000 AND 50999;
These ALTER TABLE MODIFY ADD/DROP RANGE requests only run if the session collation matches the PPI collation and the session mode matches the session mode in effect when the character PPI was created.
The following example, which is based on the same original schema, returns an error:
ALTER TABLE orders MODIFY DROP RANGE#L3 BETWEEN 'medium' AND 'medium' ADD RANGE BETWEEN 'medium' AND 'urgent';
The following ADD/DROP request involving the character row partitioning level of the orders table is valid because the request involves only the special row partitions NO RANGE OR UNKNOWN and UNKNOWN, with the NO RANGE OR UNKNOWN row partition being replaced with the UNKNOWN partition.
ALTER TABLE orders MODIFY PRIMARY INDEX DROP RANGE#L3 WHERE PARTITION#L3 = 4 ADD RANGE#L3 UNKNOWN;