Reference Partitioning in Oracle Database
Reference partitioning provides the ability to partition a table based on the partitioning scheme of the table referenced in its referential constraint. A table can now be partitioned based on the partitioning method of a table referenced in its referential constraint. Tables with a parent/child relationship can be equipartitioned by inheriting the partitioning key from the parent table without duplication of the key columns. If the parent table is a composite-partitioned table, then the table will have one partition for each subpartition of its parent.
Benefits
- Tables with a parent/child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns.
- The logical dependency also automatically cascades partition maintenance operations, making application development easier and less error prone.
- Query predicates can be different and partitionwise joins still work.
- Can be useful for nested table partitioning.
SQL> CREATE TABLE ref_part_parent
2 (pcol1 NUMBER PRIMARY KEY,
3 pcol2 VARCHAR2(10))
4 PARTITION BY RANGE (pcol1)
5 (PARTITION p1 VALUES LESS THAN (100),
6 PARTITION p2 VALUES LESS THAN (200),
7 PARTITION p3 VALUES LESS THAN (300),
8* PARTITION p4 VALUES LESS THAN (MAXVALUE))
Table created.
SQL> INSERT INTO ref_part_parent values(50,'fifty');
1 row created.
SQL> INSERT INTO ref_part_parent values(150,'1-fifty');
1 row created.
SQL> INSERT INTO ref_part_parent values(250,'2-fifty');
1 row created.
SQL> INSERT INTO ref_part_parent values(350,'3-fifty');
1 row created.
SQL> commit;
Commit complete.
SQL> CREATE TABLE ref_part_child
2 (ccol1 NUMBER NOT NULL,
3 CONSTRAINT ccol1_fk FOREIGN KEY(ccol1) REFERENCES ref_part_parent(pcol1))
4 PARTITION BY REFERENCE(ccol1_fk);
Table created.
SQL> alter table ref_part_child
2 add (ccol2 varchar2(10));
Table altered.
SQL> insert into ref_part_child values(50,'child_1');
1 row created.
SQL> insert into ref_part_child values(150,'child0-1');
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name like 'REF_PART%';
TABLE_NAME PARTITION_NAME HIGH
------------------------------ ------------------------------ ----
REF_PART_PARENT P1 100
REF_PART_PARENT P2 200
REF_PART_PARENT P3 300
REF_PART_PARENT P4 MAXV
REF_PART_CHILD P1
REF_PART_CHILD P2
REF_PART_CHILD P3
REF_PART_CHILD P4
8 rows selected.
SQL> select * from ref_part_child partition (p1);
CCOL1 CCOL2
---------- ----------
50 child_1
SQL> select * from ref_part_child partition (p4);
no rows selected
SQL> select * from ref_part_child partition (p3);
no rows selected
SQL> select * from ref_part_child partition (p2);
CCOL1 CCOL2
---------- ----------
150 child0-1
It is not possible to disable the foreign key constraint of a reference-partitioned table.
SQL> alter table ref_part_child disable constraint ccol1_fk;
alter table ref_part_child disable constraint ccol1_fk
*
ERROR at line 1:
ORA-14650: operation not supported for reference-partitioned tables
SQL> alter table ref_part_child drop constraint ccol1_fk;
alter table ref_part_child drop constraint ccol1_fk
*
ERROR at line 1:
ORA-14650: operation not supported for reference-partitioned tables
It is not permitted to add or drop partitions of a reference-partitioned table. However performing performing partition maintenance operations (PMOP) on the parent table is automatically cascaded to the child table.
SQL> alter table ref_part_parent drop partition p3;
Table altered.
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name like 'REF_PART%';
TABLE_NAME PARTITION_NAME HIGH
------------------------------ --------------- ----
REF_PART_PARENT P1 100
REF_PART_PARENT P2 200
REF_PART_PARENT P4 MAXV
REF_PART_CHILD P1
REF_PART_CHILD P2
REF_PART_CHILD P4
6 rows selected.