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.