Pickleball spielen

An existing RANGE partitioned table can easily be changed to be INTERVAL partitioned with the SET INTERVAL command. My table has been created initially like this:

SQL> create table sales_range (id number, name varchar2(20),
 amount_sold number, shop varchar2(20), time_id date)
 partition by range (time_id)
 (
 partition q1 values less than (to_date('01.04.2016','dd.mm.yyyy')),
 partition q2 values less than (to_date('01.07.2016','dd.mm.yyyy')),
 partition q3 values less than (to_date('01.10.2016','dd.mm.yyyy')),
 partition q4 values less than (to_date('01.01.2017','dd.mm.yyyy'))
 );

That way, an insert that falls out of the last existing range fails:

SQL> insert into sales_range values (4000001,'Hesse',1999,'Birmingham',to_date('27.01.2017','dd.mm.yyyy'));
insert into sales_range values (4000001,'Hesse',1999,'Birmingham',to_date('27.01.2017','dd.mm.yyyy'))
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Now instead of having to add a new range each time, the table can be changed to add that new range automatically if new rows require them. In other words, the 11g Feature Interval Partitioning can be added after the initial creation of the table:

SQL> alter table sales_range set interval(numtoYMinterval(3,'MONTH'));

Table altered.

SQL> insert into sales_range values (4000001,'Hesse',1999,'Birmingham',to_date('27.01.2017','dd.mm.yyyy'));

1 row created.

SQL> commit;

Commit complete.

SQL> col partition_name for a10
SQL> select partition_name from user_tab_partitions where table_name='SALES_RANGE'; 

PARTITION_
----------
SYS_P249
Q4
Q3
Q2
Q1

This is documented, of course. But the ALTER TABLE command is probably one of the most voluminous, so good luck in working your way through it until you find that part 😉