Partitioning - 7 : Interval Partitioning

Interval Partitioning was introduced in 11g as an enhancement to Range Partitioning, but supporting only DATE and NUMBER datatypes.  This allows you to define the interval for each Partition and leave it to the database engine to automatically create new Partitions as required when data is inserted.  Thus, you do not have to pre-create Partitions for future data.

Here is a demo with Monthly Date Intervals.

 2  (manufacture_date date,
  3   item_code varchar2(32),
  4   item_quantity number(8,0))
  5  partition by range (manufacture_date)
  6  interval (numtoyminterval(1,'MONTH'))
  7  (partition P_1 values less than (to_date('01-JUL-2018','DD-MON-YYYY')))
  8  /

Table created.

SQL> set long 32           
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'MANUFACTURING_SUMMARY'
  4  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------
P_1                            TO_DATE(' 2018-07-01 00:00:00',

SQL> 

The INTERVAL clause specifies how the upper bounds for new Partitions are to be defined.  I only need to name the boundary for the first (lowest) Partition and name the Partition.  All subsequent Partitions are automatically created with names assigned by Oracle and high values based on the INTERVAL clause.

Let me insert a few rows.

SQL> insert into manufacturing_summary
  2  (manufacture_date, item_code, item_quantity)
  3  values
  4  (to_date('29-JUN-2018','DD-MON-YYYY'), 'ABC123',4000) 
  5  /

1 row created.

SQL> insert into manufacturing_summary
  2  values (to_date('01-JUL-2018','DD-MON-YYYY'),'ABC123',3000)
  3  /

1 row created.

SQL> insert into manufacturing_summary
  2  values (to_date('01-JUL-2018','DD-MON-YYYY'),'FGH422',1000)
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'MANUFACTURING_SUMMARY'
  4  order by partition_position
  5  /

PARTITION_NAME                  HIGH_VALUE
------------------------------ --------------------------------
P_1                            TO_DATE(' 2018-07-01 00:00:00',
SYS_P519                       TO_DATE(' 2018-08-01 00:00:00',

SQL> 

Oracle automatically created Partition S_P519 for July data.

What happens if there manufactuing daa is not available from 02-Jul-2018 to, say, 04-Sep-2018 ?  And availability of data resumes only on 05-Sep-2018 ?

SQL> insert into manufacturing_summary
  2  values (to_date('05-SEP-2018','DD-MON-YYYY'),'ABC123',3000) 
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> select partition_position, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'MANUFACTURING_SUMMARY'
  4  order by partition_position
  5  /

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
HIGH_VALUE
--------------------------------
                 1 P_1
TO_DATE(' 2018-07-01 00:00:00',

                 2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

                 3 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',


SQL> 

The third Partition, SYS_P520 is created with the Upper Bound (HIGH_VALUE) of 01-Oct for the September data.

What if August data becomes available subsequently and is inserted ?

SQL> insert into manufacturing_summary
  2  values (to_date('10-AUG-2018','DD-MON-YYYY'),'ABC123',1500)
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> select partition_position, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'MANUFACTURING_SUMMARY'
  4  order by partition_position
  5  /

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
HIGH_VALUE
--------------------------------
                 1 P_1
TO_DATE(' 2018-07-01 00:00:00',

                 2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

                 3 SYS_P521
TO_DATE(' 2018-09-01 00:00:00',

                 4 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',


SQL> 

A new Partition with the HIGH_VALUE of 01-Sept did get created as SYS_P521 and inserted into the ordered position 3.  While the previously created Partition S_P520 (HIGH_VALUE 01-Oct) got renumbered to 4.  We can verify this by actually querying the Partitions.

SQL> select * from manufacturing_summary partition (SYS_P521);

MANUFACTU ITEM_CODE                        ITEM_QUANTITY
--------- -------------------------------- -------------
10-AUG-18 ABC123                                    1500

SQL>
SQL> select * from manufacturing_summary partition (SYS_P520);

MANUFACTU ITEM_CODE                        ITEM_QUANTITY
--------- -------------------------------- -------------
05-SEP-18 ABC123                                    3000

SQL> 

SYS_P520 was created first for September data although no August data existed.  SYS_P521 was created subsequently for August data which was inserted later.

Remember this : NEVER rely on Partition Names to attempt to identify what data is in a Partition.  Always use PARTITION_POSITION and HIGH_VALUE to identify the logical position (rank) and the data that is present in the Partition.

Where do the Partition names SYS_P519, SYS_P520, SYS_P521 come from ?  They are from a system defined sequence, self-managed by Oracle.

Let me demonstrate this with another example.

SQL> l
  1  create table dummy_intvl_tbl
  2  (id_col number,
  3   data_col varchar2(15))
  4  partition by range(id_col)
  5  interval (100)
  6* (partition P_1 values less than (101))
SQL> /

Table created.

SQL> insert into dummy_intvl_tbl
  2  values (50,'data1');

1 row created.

SQL>
SQL> insert into dummy_intvl_tbl
  2  values (150,'data3');

1 row created.

SQL> 
SQL> insert into manufacturing_summary
  2  values (to_date('25-OCT-2018','DD-MON-YYYY'),'FGH422',500);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select table_name, partition_position, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name in ('MANUFACTURING_SUMMARY','DUMMY_INTVL_TBL')
  4  order by 1,2
  5  /

TABLE_NAME                     PARTITION_POSITION PARTITION_NAME
------------------------------ ------------------ ------------------------------
HIGH_VALUE
--------------------------------
DUMMY_INTVL_TBL                                 1 P_1
101

DUMMY_INTVL_TBL                                 2 SYS_P525
201

MANUFACTURING_SUMMARY                           1 P_1
TO_DATE(' 2018-07-01 00:00:00',

MANUFACTURING_SUMMARY                           2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

MANUFACTURING_SUMMARY                           3 SYS_P521
TO_DATE(' 2018-09-01 00:00:00',

MANUFACTURING_SUMMARY                           4 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',

MANUFACTURING_SUMMARY                           5 SYS_P526
TO_DATE(' 2018-11-01 00:00:00',


7 rows selected.

SQL> 

Note how Partition Name SYS_P525 was allocated to DUMMY_INTVL_TBL and then P_526 to MANUFACTURING_SUMMARY.
These System Defined Partition names use a *global* sequence, not tied to a specific table.

Can you rename the System Defined Partition after it has been automatically created ?

SQL> alter table manufacturing_summary
  2  rename partition SYS_P519 to Y18M07
  3  /

Table altered.

SQL> alter table manufacturing_summary
  2  rename partition SYS_P520 to Y18M09
  3  /

Table altered.

SQL> alter table manufacturing_summary
  2  rename partition SYS_P521 to Y18M08
  3  /

Table altered.

SQL> alter table manufacturing_summary
  2  rename partition SYS_P526 to Y18M10 
  3  /

Table altered.

SQL> 
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'MANUFACTURING_SUMMARY'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------
P_1                            TO_DATE(' 2018-07-01 00:00:00',
Y18M07                         TO_DATE(' 2018-08-01 00:00:00',
Y18M08                         TO_DATE(' 2018-09-01 00:00:00',
Y18M09                         TO_DATE(' 2018-10-01 00:00:00',
Y18M10                         TO_DATE(' 2018-11-01 00:00:00',

SQL> 

Yes, fortunately, you CAN rename the Partitions *after* they are automatically created.