RANGE - Interval Partitioning In Oracle

Range Interval Partitioning In Oracle

Interval Partitioning has been introduced in oracle 11g.
With this method, we can automate the creation of range partition .
While creating the partitioned table, we just need to define one partition.
New partitions will be created automatically based on interval criteria when the data is inserted to the table. We don’t need to create the future partitions.

1. Lets create a monthly partitioned table ( with interval).

CREATE TABLE DBATEST.PART_TEST_TABLE ( CORRELATION_ID VARCHAR2(100 BYTE), ATTRIBUTE_KEY VARCHAR2(50 BYTE), ATTRIBUTE_VALUE VARCHAR2(100 BYTE), EVENT_TIMESTAMP TIMESTAMP(6) ) TABLESPACE STCDBA PARTITION BY RANGE (EVENT_TIMESTAMP) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION PART_TEST_TABLE_MIN VALUES LESS THAN (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) );

CREATE INDEX DBATEST.PART_TEST_TABLE_COR_ID_IDX ON DBATEST.PART_TEST_TABLE (CORRELATION_ID) LOCAL; Index created.

Here the date 2016-05-01 is known as TRANSIT POINT . Any data inserted beyond this transit point will led to creation of a new partition automatically.

INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point



2. Now check dba_tab_partitions

SELECT table_name,partition_name,high_value from dba_tab_partitions where table_name='PART_TEST_TABLE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------------- ---------------------------------- --------------------------------------------------------------------- PART_TEST_TABLE PART_TEST_TABLE_MIN TIMESTAMP' 2016-05-01 00:00:00'


3. Lets insert some data and check the dba_tab_partitions:

insert into dbatest.PART_TEST_TABLE values ('387fade4-f0f4-4bc3-9ba3-41458fad7a4c','ApplicationName','ULA','01-JUN-2016') commit; SELECT table_name,partition_name,high_value,interval  from dba_tab_partitions where table_name='PART_TEST_TABLE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------------- ---------------------------------- --------------------------------------------------------------------- PART_TEST_TABLE PART_TEST_TABLE_MIN TIMESTAMP' 2016-05-01 00:00:00'


We can see a new monthly partition has been created automatically .

Similarly

for weekly partition use the parameter – INTERVAL (numtodsinterval(7,’day’))
for yearly partition use the parameter – INTERVAL (NUMTOYMINTERVAL(1,’YEAR’))



Convert Existing Range partitioned table to interval partition:
ALTER TABLE dbatest.range_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));


Restriction of Interval Partitioning:

1. Cannot be created for Index organized table(IOT)

2. The partitioning column can be only one and it must be of type NUMBER or DATE

3. Cannot use the MAXVALUE clause