Interval Reference Partitioning Oracle Database 12C release 1 (12.1)

In 12C Oracle has extended 11G reference partitioning. Now It’s possible to use interval partitioned tables as parent tables for reference partitioning.

Requirements and features

  • Requires compatibility set to 12.0.0.0 or higher
  • ALTER TABLE SET INTERVAL can be set for parent but not for reference-partitioned child tables
  • ALTER TABLE SET STORE IN can be set for parent but not for reference-partitioned child tables
  • ALTER TABLE SPLIT PARTITION that transforms interval partitions to conventional partitions in parent table construct the corresponding transformation in the child table, creating partitions in the child table as necessary.

Let’s create simple example. Parent table parent_tbl is using RANGE INTERVAL partitioning and child table child_tbl are reference partitioned. Such combination was not possible in previous releases 11g.

create table parent_tbl
(
  id1 number, 
  id2 number,
  constraint parent_tbl_pk primary key(id1)
)
partition by range(id2) INTERVAL(10)
(
  partition p1 values less than (10)
);

create table child_tbl
(
  id1 number, 
  id2 number not null,
  constraint child_tbl_fk foreign key(id2) references parent_tbl
)
partition by REFERENCE(child_tbl_fk);

At the beginning both tables have got only one single partition P1

select 
  table_name, partition_name, partition_position, 
  high_value, interval
from user_tab_partitions 
where table_name in ('PARENT_TBL', 'CHILD_TBL')
order by table_name, partition_position;

TABLE_NAME  PARTITION_NAME  PARTITION_POSITION HIGH_VALUE  INTERVAL
----------- --------------- ------------------ ----------- ---------
CHILD_TBL   P1              1                              NO
PARENT_TBL  P1              1                  10          NO

Adding data to parent table creates new INTERVAL partitions in the table

insert into parent_tbl values(15, 15);
insert into parent_tbl values(25, 25);
insert into parent_tbl values(35, 35);

commit;

select 
  table_name, partition_name, partition_position, 
  high_value, interval
from user_tab_partitions 
where table_name in ('PARENT_TBL', 'CHILD_TBL')
order by table_name, partition_position;

TABLE_NAME  PARTITION_NAME  PARTITION_POSITION HIGH_VALUE  INTERVAL
----------- --------------- ------------------ ----------- ---------
CHILD_TBL   P1              1                              NO
PARENT_TBL  P1              1                  10          NO
PARENT_TBL  SYS_P564        2                  20          YES
PARENT_TBL  SYS_P565        3                  30          YES
PARENT_TBL  SYS_P566        4                  40          YES

It’s the same happen if you add data to child table. Name of new partition(s) in child table is copied from parent table.

insert into child_tbl values(25, 25);

commit;

select 
  table_name, partition_name, partition_position, 
  high_value, interval
from user_tab_partitions 
where table_name in ('PARENT_TBL', 'CHILD_TBL')
order by table_name, partition_position;

TABLE_NAME  PARTITION_NAME  PARTITION_POSITION HIGH_VALUE  INTERVAL
----------- --------------- ------------------ ----------- ---------
CHILD_TBL   P1              1                              NO
CHILD_TBL   SYS_P565        2                              YES
PARENT_TBL  P1              1                  10          NO
PARENT_TBL  SYS_P564        2                  20          YES
PARENT_TBL  SYS_P565        3                  30          YES
PARENT_TBL  SYS_P566        4                  40          YES

If the interval partition is split in the parent table, then some interval partitions are converted to conventional partitions for all tables in the hierarchy, creating conventional partitions in the child table in the process

alter table parent_tbl 
split partition for (25) at (25)
into (partition p3, partition p4);

select 
  table_name, partition_name, partition_position, 
  high_value, interval
from user_tab_partitions 
where table_name in ('PARENT_TBL', 'CHILD_TBL')
order by table_name, partition_position;

TABLE_NAME  PARTITION_NAME  PARTITION_POSITION HIGH_VALUE  INTERVAL
----------- --------------- ------------------ ----------- ---------
CHILD_TBL   P1              1                              NO
CHILD_TBL   SYS_P565        2                              NO
CHILD_TBL   P3              3                              NO
CHILD_TBL   P4              4                              NO
PARENT_TBL  P1              1                  10          NO
PARENT_TBL  SYS_P564        2                  20          NO
PARENT_TBL  P3              3                  25          NO
PARENT_TBL  P4              4                  30          NO
PARENT_TBL  SYS_P566        5                  40          YES

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *