Example - Setting an Interval Range Partition v13

The example that follows sets an interval range partition of the sales table from range partitioning to start using monthly interval range partitioning. Use the following command to create 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')
);

To set the interval range partitioning from the sales table, invoke the following command:

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)

Now, 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

Then, 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)