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 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') );
 * Create Parent Table

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 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);
 * Create Child table with reference to the Parent table.

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'
 * Same partition key is created.

SQL> alter table CUSTOMERS add PARTITION pC values ('C'); Table altered
 * Change in partitions of the parent table will also cascade to that of child table.

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'