Oracle 19c: Interval Partitioning (1/2)

Interval partitioning can be used for almost every table that is range partitioned and uses fixed intervals for new partitions. more info

The benefit of interval partitioning is that you do not need to create your range partitions explicitly.

Create a table with Interval Partitionning:

First partition: table_interval_partitioning_part_01 : interval less than 01-04-2024

Second partiton: table_interval_partitioning_part_02 : interval less than 01-07-2024

create table table_interval_partitioning (id integer, hire_date date);
PARTITION by range (hire_date)
interval (NUMTOYMINTERVAL(3,'MONTH'))
(
	partition table_interval_partitioning_part_01 VALUES LESS THAN (TO_DATE('01-04-2024', 'DD-MM-YYYY')),
    partition table_interval_partitioning_part_02 VALUES LESS THAN (TO_DATE('01-07-2024', 'DD-MM-YYYY'))
);

After insert rows in correct intervals

insert into table_interval_partitioning values (1, '02/01/2024');
insert into table_interval_partitioning values (2, '05/02/2024');

Check partitions:

Now, insert a row in not existing partition:

We are going to insert a won in November, means in the period “01/10/2024 until 01/01/2025”

insert into table_interval_partitioning values (3, '25/11/2024');

Note: Oracle will create a system named partitions like SYS_P{ID_partition} like SYS_P381

Check Segment creation:

Enjoy!