Assume that the orders table defined in Table Definitions for Examples exists and the following save table has been created to handle rows that are no longer valid when you change the row partitioning for the orders table.
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, o_orderpriority CHARACTER(21), o_clerk CHARACTER(16), o_shippriority INTEGER, o_comment VARCHAR(79)) UNIQUE PRIMARY INDEX (o_orderkey);
The following ALTER TABLE request is valid if there are 0 or more rows with o_orderdate between January 1, 1992 and December 31, 1992 (DATE '1992-01-01 AND DATE '1992-12-31').
The MODIFY option moves any rows having those values into the old_orders save table and then deleted from orders.
ALTER TABLE orders MODIFY DROP RANGE BETWEEN DATE '1992-01-01' AND DATE '1992-12-31' ADD RANGE BETWEEN DATE '1999-01-01' AND DATE '2000-12-31' EACH INTERVAL '1' MONTH WITH INSERT INTO old_orders;
Suppose orders had been defined as a column-partitioned table, orders_cp.
The following ALTER TABLE request is valid if there are 0 or more rows with o_orderdate between January 1, 1992 and December 31, 1992 (DATE '1992-01-01 AND DATE '1992-12-31').
The MODIFY option moves any rows having those values into the old_orders save table and then deleted from orders_cp.
ALTER TABLE orders_cp MODIFY DROP RANGE BETWEEN DATE '1992-01-01' AND DATE '1992-12-31' ADD RANGE BETWEEN DATE '1999-01-01' AND DATE '2000-12-31' EACH INTERVAL '1' MONTH WITH INSERT INTO old_orders;