Reference partitioning addresses the issue of parent/child equipartitioning; that is, when you need the child table to be partitioned in such a manner that each child table partition has a one-to-one relationship with a parent table partition.

This is important in situations such as a data warehouse where you want to keep a specific amount of data online (say the last five years’ worth of ORDER information) and need to ensure the related child data(theORDER_LINE_ITEMS data) is online as well. In this classic example, the ORDERS table would typically have a column ORDER_DATE, making it easy to partition by month and thus facilitate keeping the last five years of data online easily.

As time advances, you would just have next month’s partition available for loading, and you would drop the oldest partition. However, when you consider the ORDER_LINE_ITEMS table, you can see you would have a problem. It does not have the ORDER_DATE column, and there is nothing in the ORDER_LINE_ITEMS table to partition it by; therefore, it’s not facilitating the purging of old information or loading of new information.

In the past, prior to reference partitioning, developers would have to denormalize the data, in effect copying the ORDER_DATE attribute from the parent table ORDERS into the child ORDER_LINE_ITEMS table.

This presented the typical problems of data redundancy, that of increased storage overhead, increased data loading resources, cascading update issues (if you modify the parent, you have to ensure you update all copies of the parent data), and so on.

Additionally, if you enabled foreign key constraints in the database (as you should), you would discover that you lost the ability to truncate or drop old partitions in the parent table.

For example, let’s set up the conventional ORDERS and ORDER_LINE_ITEMS tables starting with the ORDERS table:
$ sqlplus eoda/foo@PDB1
SQL> create table orders(order# number primary key,order_date date,data varchar2(30))enable row movementPARTITION BY RANGE (order_date)(PARTITION part_2020 VALUES LESS THAN (to_date(’01-01-2021′,’dd-mm-­yyyy’)) , PARTITION part_2021 VALUES LESS THAN (to_date(’01-01-2022′,’dd-mm-­yyyy’)));Table created.
SQL> insert into orders values (1, to_date( ’01-jun-2020′, ‘dd-mon-yyyy’ ), ‘xxx’ ); 1 row created.
SQL> insert into orders values (2, to_date( ’01-jun-2021′, ‘dd-mon-yyyy’ ), ‘xxx’ ); 1 row created.

And now we’ll create the ORDER_LINE_ITEMS table—with a bit of data pointing to theORDERS table:
SQL> create table order_line_items(order# number,line# number,order_date date, — manually copied from ORDERS!data varchar2(30),constraint c1_pk primary key(order#,line#),constraint c1_fk_p foreign key(order#) references orders)enable row movementPARTITION BY RANGE (order_date)(PARTITION part_2020 VALUES LESS THAN (to_date(’01-01-2021′,’dd-mm-­yyyy’)) , PARTITION part_2021 VALUES LESS THAN (to_date(’01-01-2022′,’dd-mm-­yyyy’)));Table created.
SQL> insert into order_line_items values ( 1, 1, to_date( ’01-jun-2020′,’dd-mon-yyyy’ ), ‘yyy’ );1 row created.
SQL> insert into order_line_items values ( 2, 1, to_date( ’01-jun-2021′,’dd-mon-yyyy’ ), ‘yyy’ );1 row created.

Now, if we were to drop the ORDER_LINE_ITEMS partition containing 2020 data, you know and I know that the corresponding ORDERS partition for 2020 could be dropped as well, without violating the referential integrity constraint. You and I know it, but the database is not aware of that fact:
SQL> alter table order_line_items drop partition part_2020; Table altered.
SQL> alter table orders drop partition part_2020; alter table orders drop partition part_2020 *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

So, not only is the approach of denormalizing the data cumbersome, resource intensive, and potentially damaging to our data integrity, it prevents us from doing something we frequently need to do when administering partitioned tables: purging old information.

Enter reference partitioning. With reference partitioning, a child table will inherit the partitioning scheme of its parent table without having to denormalize the partitioning key, and it allows the database to understand that the child table is equipartitioned with the parent table. That is, we’ll be able to drop or truncate the parent table partition when we truncate or drop the corresponding child table partition.