Example - INTERVAL RANGE PARTITION v13
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)