This topic describes how to switch between range partitioning and interval range partitioning.

Change a range-partitioned table to an interval range-partitioned table

Execute the ALTER TABLE...SET INTERVAL statement to change an existing range-partitioned table to an interval range-partitioned table.

After you set a range or a time interval, the database automatically creates a partition based on the specified range or time interval. Data is then inserted into the new partition.

The statement uses the following format:

ALTER TABLE <table_name> SET INTERVAL (<constant> | <expression>);

For information about the INTERVAL parameter, see Interval range partitioning.

Change an interval range-partitioned table to a range-partitioned table

Execute the SET INTERVAL() statement to disable interval range partitioning. After you disable interval range partitioning, the database changes an interval range-partitioned table to a range-partitioned table. The range or time interval for implementing interval range partitioning is changed to the range or time interval for implementing range partitioning.

The statement uses the following format:

ALTER TABLE <table_name> SET INTERVAL ();

Examples

The following example shows how to change the sales table from a range-partitioned table to an interval range-partitioned table and insert data into it:

Create a range-partitioned table that is named sales, as shown in the following example:

CREATE TABLE sales
(
  prod_id           int,
  prod_quantity     int,
  sold_month        date
)
PARTITION BY RANGE(sold_month)
(
  PARTITION p1
    VALUES LESS THAN('15-JAN-2019'),
  PARTITION p2
    VALUES LESS THAN('15-FEB-2019')
);

Query the ALL_TAB_PARTITIONS view, as shown in the following example:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

The following result is returned:

 partition_name |      high_value
----------------+----------------------
 P1             | FOR VALUES FROM ('15-JAN-19 00:00:00') TO ('15-FEB-19 00:00:00')
 P2             | FOR VALUES FROM (MINVALUE) TO ('15-JAN-19 00:00:00')
(2 rows)

Change the range-partitioned table to an interval range-partitioned table, as shown in the following example: 

ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));

Insert the data that exceeds the range partition, as shown in the following example:

INSERT INTO sales VALUES (1,100,'05-APR-2019');
INSERT 0 1

After you insert the data, query the ALL_TAB_PARTITIONS view again, as shown in the following example: 

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

If the data is inserted, the system generates a name for the interval range partition. The name varies based on sessions. The following result is returned: 

 partition_name |      high_value
----------------+----------------------
 SYS596430103   | FOR VALUES FROM ('15-MAR-19 00:00:00') TO ('15-APR-19 00:00:00')
 P1             | FOR VALUES FROM ('15-JAN-19 00:00:00') TO ('15-FEB-19 00:00:00')
 P2             | FOR VALUES FROM (MINVALUE) TO ('15-JAN-19 00:00:00')
(3 rows)