Partitioning -- 8 : Reference Partitioning

Like Interval Partitioning, another enhancement in 11g is Reference Partitioning.

Reference Partitioning allows you to use a Referential Integrity Constraint to equi-partition a "Child" Table with a "Parent" Table.

Here is a quick demonstration :

SQL> l
  1  create table orders
  2  (order_id  number primary key,
  3   order_date date not null,
  4   customer_id number)
  5  partition by range (order_date)
  6  (partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
  7   partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
  8* )
SQL> /

Table created.

SQL> 
SQL> l
  1  create table order_lines
  2  (line_unique_id  number primary key,
  3   order_id number not null,
  4   order_line_id  number,
  5   product_id  number,
  6   product_quantity number,
  7   constraint order_lines_fk foreign key (order_id)
  8    references orders(order_id)
  9  )
 10* partition by reference (order_lines_fk)
SQL> /

Table created.

SQL> 
SQL> col high_value format a28 trunc
SQL> col table_name format a16
SQL> col partition_name format a8
SQL> select table_name, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name in ('ORDERS','ORDER_LINES')
  4  order by table_name, partition_position
  5  /

TABLE_NAME       PARTITIO HIGH_VALUE
---------------- -------- ----------------------------
ORDERS           P_2017   TO_DATE(' 2018-01-01 00:00:0
ORDERS           P_2018   TO_DATE(' 2019-01-01 00:00:0
ORDER_LINES      P_2017
ORDER_LINES      P_2018

SQL> 

Notice the "automatically" created Partitions for the ORDER_LINES ("Child") Table that match those for the ORDERS ("Parent") Table.

.
.
.