Example: Setting an interval range partition v18

This example sets an interval range partition of the sales table. It converts the table from range partitioning to start using monthly interval range partitioning.

This command creates the sales table:

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')
);

Set the interval range partitioning from the sales table:

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

Query the ALL_TAB_PARTITIONS view before a database creates an interval range partition:

edb=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;

 partition_name |      high_value
----------------+----------------------
 P1             | '15-JAN-19 00:00:00'
 P2             | '15-FEB-19 00:00:00'
(2 rows)

Add data to the sales table that exceeds the high value of a range partition:

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

Query the ALL_TAB_PARTITIONS view again after the INSERT statement. The interval range partition is successfully created and data is inserted. A system-generated name of the interval range partition is created that varies for each session.

edb=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;

 partition_name |      high_value
----------------+----------------------
 P1             | '15-JAN-19 00:00:00'
 P2             | '15-FEB-19 00:00:00'
 SYS916340103   | '15-APR-19 00:00:00'
(3 rows)