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