Reference Partitioning - Oracle 11g New Feature

From Oracle 11g we can create partitions on reference key, i.e. if we have created the partitions of parent table, by using a new feature called Reference Partitioning we can create partitions identical to parent table in child table, even if the coloum used to create the partitions does not exist in child table.

Let say we have following relationship of departments & employee table.

create table departments
(
  department_id    number(4),
  department_name  varchar2(30),
  manager_id       number(6),
  location_id      number(4)
)
partition by list (location_id)
(
   partition dept_p1 values(1),
   partition dept_p2 values(2),
   partition dept_p3 values(3),
   partition dept_p4 values(4),
   partition dept_p5 values(5),
   partition dept_p6 values(default),
);

create table employees
(
  employee_id     number(6),
  first_name      varchar2(20),
  last_name       varchar2(25),
  email           varchar2(25),
  phone_number    varchar2(20),
  hire_date       date,
  job_id          varchar2(10),
  salary          number(8,2),
  commission_pct  number(2,2),
  manager_id      number(6),
  department_id   number(4),
  constraint  fk_employees_01 (department_id) 
references departments
);

Here departments table is partitioned by location_id. If we wish to make partitions of employee table identical to departments table, Prior to 11g it was not possible because employee table does not have location_id. New with Oracle 11g we can create partitions on reference key, called Reference Partitioning as below:

create table employees
(
  employee_id     number(6),
  first_name      varchar2(20),
  last_name       varchar2(25),
  email           varchar2(25),
  phone_number    varchar2(20),
  hire_date       date,
  job_id          varchar2(10),
  salary          number(8,2),
  commission_pct  number(2,2),
  manager_id      number(6),
  department_id   number(4),
  constraint  fk_employees_01 (department_id) 
references departments
)
partition by reference (fk_employees_01);

Related Links:
- New Features for Developers in Oracle 11g