Reference partitioning
Jump to navigation
Jump to search
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'