Reference partitioning

What is Reference Partitioning?

• A table can now be partitioned based on the partitioning method of a table referenced in its referential constraint.
• The partition key is resolved through an existing parent/child relationship.
• The partitioning key is enforced by active primary key and foreign key constraints. It is not possible to disable the FK of a reference-partitioned table.
• Tables with a parent/child relationship can be equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns
• Partitions are automatically maintained – any partition maintenance operations on the parent table will be cascaded to the child table.

Demo

• Create Parent Table

create table customers
(
   cust_id   number primary key,
   cust_name varchar2(200),
   rating    varchar2(1) not null
)
partition by list (rating)
(
   partition pA values ('A'),
   partition pB values ('B')
);

SQL> SELECT table_name, partition_name, high_value 
FROM USER_tab_partitions
WHERE table_name='CUSTOMERS';
 
TABLE_NAME PARTITION_NAME  HIGH_VALUE
---------- --------------- ----------
CUSTOMERS  PA              'A'
CUSTOMERS  PB              'B'

• Create Child table with reference to the Parent table

create table sales
(
   sales_id    number primary key,
   cust_id     number not null,
   sales_amt   number,
   constraint  FK_SALES_01
        foreign key (cust_id)
        references customers 
)
partition by reference (FK_SALES_01);

• Same partition key is created.

SQL> SELECT table_name, partition_name, high_value 
FROM USER_tab_partitions
WHERE table_name='SALES';
 
TABLE_NAME PARTITION_NAME  HIGH_VALUE
---------- --------------- ----------
SALES      PA              'A'
SALES      PB              'B'

• Change in partitions of the parent table will also cascade to that of child table.

SQL> alter table CUSTOMERS add PARTITION pC values ('C');
Table altered

SQL> SELECT table_name, partition_name, high_value 
FROM USER_tab_partitions
WHERE table_name='CUSTOMERS';
 
TABLE_NAME PARTITION_NAME  HIGH_VALUE
---------- --------------- ----------
CUSTOMERS  PA              'A'
CUSTOMERS  PB              'B‘
CUSTOMERS  PC              'C'

SQL> SELECT table_name, partition_name, high_value 
FROM USER_tab_partitions
WHERE table_name='SALES';
 
TABLE_NAME PARTITION_NAME  HIGH_VALUE
---------- --------------- ----------
SALES      PA              'A'
SALES      PB              'B‘
SALES      PC              'C'