Assume you have created a column-partitioned version of the orders table from the previous example named orders_cp with column partitioning at level 1.
The following ALTER TABLE request is valid only if orders_cp is not populated with data.
ALTER TABLE orders_cp MODIFY PARTITION BY (COLUMN, 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 48, RANGE_N(o_orderstatus = 'S', o_orderstatus = 'C', o_orderstatus = 'F', o_orderstatus = 'T', NO CASE OR UNKNOWN) );
Assuming the previous request succeeded because orders_cp was empty, you could 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 partitioning expression for level 3.
ALTER TABLE orders_cp MODIFY DROP RANGE#L3 BETWEEN DATE '2000-01-01' AND DATE '2000-12-31' ADD RANGE BETWEEN DATE '2007-01-01' AND DATE '2008-12-31' EACH INTERVAL '1' MONTH; ALTER TABLE orders_cp MODIFY DROP RANGE#L3 WHERE orders_cp.PARTITION#L2 BETWEEN 1 AND 12 ADD RANGE#L3 BETWEEN DATE '2007-01-01' AND DATE '2008-12-31' EACH INTERVAL '1' MONTH; ALTER TABLE orders_cp MODIFY DROP RANGE#L3 WHERE PARTITION#L3 IN (1,2,3,4,5,6,7,8,9,10,11,12) ADD RANGE BETWEEN DATE '2007-01-01' AND DATE '2008-12-31' EACH INTERVAL '1' MONTH;
Assuming that orders_cp is column-partitioned, the following ALTER TABLE … MODIFY request is valid even if the table is populated with data.
ALTER TABLE orders_cp MODIFY DROP RANGE#L2 BETWEEN 0 AND 999 ADD RANGE BETWEEN 50000 AND 54999 EACH 100;
These ALTER TABLE MODIFY ADD/DROP RANGE requests only execute if the session collation and the session mode are the same as when the table was created.
The following example, which is based on the same original schema, returns an error if either the session collation or session mode are different than when orders_cp was created.
ALTER TABLE orders_cp MODIFY DROP RANGE#L3 BETWEEN 'medium' AND 'medium' ADD RANGE BETWEEN 'medium' AND 'urgent';
The following ADD/DROP request involving the character partitioning level of the orders_cp table is valid because it 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. Vantage deletes any rows that do not belong to any of the defined ranges for orders_cp
ALTER TABLE orders_cp MODIFY DROP RANGE#L4 WHERE PARTITION#L3 = 4 ADD RANGE#L4 UNKNOWN WITH DELETE;