Example - INTERVAL RANGE PARTITION v13

Suggest edits

The following example shows a (sales) table that is 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 into 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')
);

First, query the ALL_TAB_PARTITIONS view before an interval range partition is created by the database.

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

Then, 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 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-MAY-19 00:00:00'
(3 rows)

← Prev

Example - PARTITION BY RANGE

Example - PARTITION BY HASH

Example - INTERVAL RANGE PARTITION v13

← Prev

Next →