Assume you have created a table named orders using the following CREATE TABLE request.
CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHARACTER(1) CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL) PRIMARY INDEX (o_orderkey) PARTITION BY ( RANGE_N(o_custkey BETWEEN 0 AND 49999 EACH 100), RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH)) UNIQUE INDEX (o_orderkey);
This table has 2-byte partitioning. If orders is empty, you could submit the following ALTER TABLE MODIFY request to modify it to have a single-level row-partitioned primary index:
ALTER TABLE orders MODIFY PARTITION BY RANGE_N( o_orderdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH);
Assume the previously defined CREATE TABLE definition for orders. If there are no rows with o_orderdate between the dates 2000-01-01 and 2000-12-31, you could submit any one of the following equivalent ALTER TABLE requests to modify the 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;
or
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;
or
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;
Assume the previously defined CREATE TABLE definition for orders. If there are zero, one, or more rows with o_orderdate between 2000-01-01 and 2000-12-31, you could submit the following ALTER TABLE MODIFY request to alter the row partitioning. In this case, the system deletes the rows from the dropped ranges.
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 WITH DELETE;
Assume you created one table using the previously defined CREATE TABLE definition for orders and another table using the following CREATE TABLE request.
CREATE TABLE old_orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHARACTER(1) CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL) UNIQUE PRIMARY INDEX (o_orderkey);
The following ALTER TABLE MODIFY request is one way to alter the partitioning if there are zero, one, or more rows with o_orderdate between 2000-01-01 and 2000-12-31. In this case, the system saves the rows in old_orders prior to deleting them from orders.
ALTER TABLE orders MODIFY DROP RANGE#L2 BETWEEN DATE '2000-01-01' AND DATE '2000-12-31' ADD RANGE#L2 BETWEEN DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH WITH INSERT INTO old_orders;
The following ALTER TABLE MODIFY request is not valid for a populated table because adding more partitions to level 2 exceeds the maximum defined for level 2. This is because there was no ADD option for level 2, and all the excess combined partitions were assigned to level 1 as its default ADD option.
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 '2008-12-31' EACH INTERVAL '1' MONTH WITH DELETE;
Assume the previously defined CREATE TABLE definition for orders_cp. You could submit the following ALTER TABLE MODIFY request to alter the row partitioning expressions at both levels:
ALTER TABLE orders_cp MODIFY PRIMARY INDEX DROP RANGE BETWEEN 0 AND 99 ADD RANGE BETWEEN 50000 AND 50199 EACH 100, DROP RANGE WHERE PARTITION#L2 = 1 WITH DELETE;
The number of partitions at level 1 is increased by 2 and the number of partitions at level 2 decreases by 1. At the same time, the ADD value for level 2 increases from 0 to 1.