Example: INTERVAL RANGE PARTITION v17

This example shows a sales table that's partitioned by interval on the sold_month column. The range partition is created to establish a transition point, and new partitions are created beyond that transition point. The database creates a new interval range partition and adds data to a table.

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

Query the ALL_TAB_PARTITIONS view before the 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)

Insert data into a sales table that exceeds the high value of a range partition:

edb=# INSERT INTO sales VALUES (1,200,'10-MAY-2019');
INSERT 0 1

Query the ALL_TAB_PARTITIONS view again after the insert. The data is successfully inserted, and a system-generated name of the interval range partition is created. The name 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-MAY-19 00:00:00'
(3 rows)