Assume you create orders_cp, a column-partitioned version of the orders table that has column partitioning at level 1.
If orders_cp is not populated with data, you could submit the following ALTER TABLE … MODIFY request to modify it to have only one level of row partitioning.
ALTER TABLE orders_cp MODIFY PARTITION BY (COLUMN, RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH));
Table orders_cp has 2-byte partitioning.
If there are no rows with o_orderdate in orders_cp between the dates 2000-01-01 and 2000-12-31, you could submit any one of the following equivalent ALTER TABLE … MODIFY requests to modify the partitioning expression for level 2.
ALTER TABLE orders_cp MODIFY DROP RANGE#L2 BETWEEN DATE '2000-01-01' AND DATE '2000-12-31' ADD RANGE#L2 BETWEEN DATE '2007-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH;
or
ALTER TABLE orders_cp MODIFY DROP RANGE WHERE orders.cp.PARTITION#L2 BETWEEN 1 AND 12 ADD RANGE#L2 BETWEEN DATE '2007-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH;
or
ALTER TABLE orders_cp 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 '2011-12-31' EACH INTERVAL '1' MONTH;
If there are 0, 1, or more rows with o_orderdate between 2000-01-01 and 2000-12-31 in orders_cp, you could submit the following ALTER TABLE request to alter the row partitioning level of orders_cp. In this case, Vantage deletes the rows in the dropped ranges because of the WITH DELETE specification.
This ALTER TABLE … MODIFY request is valid for a populated table because adding more partitions to level 2 does not exceed the maximum defined for level 2. This is because all the excess combined partitions were assigned to level 2 as its default ADD option.
ALTER TABLE orders_cp MODIFY DROP RANGE#L2 BETWEEN DATE '2000-01-01' AND DATE '2000-12-31' ADD RANGE BETWEEN DATE '2007-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH WITH DELETE;
Assume you create 1 table using the previously defined orders_cp and another table called old_orders.
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 request is one way to alter the row partitioning if there are 0, 1, 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 _cp.
ALTER TABLE orders_cp MODIFY DROP RANGE BETWEEN DATE '2000-01-01' AND DATE '2000-12-31' ADD RANGE BETWEEN DATE '2007-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH WITH INSERT INTO old_orders;
Assume the previously defined CREATE TABLE definition for orders (see Example: Modifying Partitioning for Multilevel Partitioned Tables) with column partitioning added at level one and the primary index has been removed. The new table is named orders_cp. Table orders_cp has 8-byte partitioning. You could submit the following ALTER TABLE request to alter the partitioning expressions at both levels:
ALTER TABLE orders_cp MODIFY DROP RANGE BETWEEN 0 AND 399 ADD RANGE BETWEEN 50000 AND 50899 EACH 100, DROP RANGE WHERE PARTITION#L3 = 1 ADD RANGE BETWEEN DATE '2007-01-01' AND DATE '2007-01-31' WITH DELETE;
The number of partitions at level 2 is increased by 5 and the number of partitions at level 3 must remain the same because the default for level 3 is ADD 0, and the excess combined combinations are assigned to level 2, not leaving enough for level 3 to be able to increase its number of partitions.