The following ALTER TABLE request modifies the table created in Table Definitions for Examples. The request is valid if there are 0 or more rows with o_orderdate values between January 1 1992 (DATE '1992-01-01') and December 31 1992 (DATE '1992-12-31'). As a result of the WITH DELETE specification, any rows that do not belong to any partition in the new partitioning expression are deleted from orders.
The specified primary index is the same as the existing primary index for the table because the request does not include changes to the primary index. The EACH clause after the DROP clause is ignored, but the EACH clause after the ADD clause is processed.
ALTER TABLE orders MODIFY DROP RANGE BETWEEN DATE '1992-01-01' AND DATE '1992-12-31' EACH INTERVAL '1' MONTH ADD RANGE BETWEEN DATE '1999-01-01' AND DATE '2000-12-31' EACH INTERVAL '1' MONTH WITH DELETE;
The following request modifies the partitioning for the column-partitioned table 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 DELETE;