Reference partitioning example

create table orders (
  order_id    number not null,
  order_date  date not null,
  customer_id number not null
 ) tablespace tbs1
partition by range (order_date) (
  partition orders_y2011 values less than (to_date('01-JAN-2012', 'dd-mon-yyyy')) tablespace tbs1,
  partition orders_y2012 values less than (to_date('01-JAN-2013', 'dd-mon-yyyy')) tablespace tbs1,
  partition orders_y2013 values less than (to_date('01-JAN-2014', 'dd-mon-yyyy')) tablespace tbs1
 );
  
alter table orders add constraint orders_pk primary key(order_id) using index tablespace tbs1;

create index orders_ix1 on orders(customer_id) 
tablespace tbs1
local
(
  partition orders_ix1_y2011 tablespace tbs1,
  partition orders_ix1_y2012 tablespace tbs1,
  partition orders_ix1_y2013 tablespace tbs1
);

-- 1. Referential constraint must be defined while creating table.
-- 2. PARTITION BY REFERENCE clause should be used for reference partitioning
--    and it should mention referential constraint name.
create table order_items (
  order_id    number not null,
  product_id  number not null,
  price       number,
  quantity    number,
  constraint order_items_fk foreign key (order_id) references orders
) tablespace tbs1
partition by reference (order_items_fk) (
  partition order_items_y2011 tablespace tbs1,
  partition order_items_y2012 tablespace tbs1,
  partition order_items_y2013 tablespace tbs1
);

alter table order_items add constraint order_items_pk primary key(order_id, product_id) using index tablespace tbs1;

create index order_items_ix1 on order_items(order_id)  
tablespace tbs1 
local
(
  partition order_items_ix1_y2011 tablespace tbs1,
  partition order_items_ix1_y2012 tablespace tbs1,
  partition order_items_ix1_y2013 tablespace tbs1
);
 
-- While adding partition to parent table, we can use 
-- 1. DEPENDENT TABLES clause to specify names of child partitions
-- 2. In UPDATE INDEXES clause, we can give indexes of parent and child tables. 
alter table orders 
  add partition orders_y2014 
     values less than (to_date('01-JAN-2015', 'dd-mon-yyyy')) 
     tablespace tbs1
  dependent tables (
     order_items (partition order_items_y2014 tablespace tbs1) 
     )
  update indexes (
     orders_ix1 (partition orders_ix1_y2014 tablespace tbs1),
     order_items_ix1 (partition order_items_ix1_y2014 tablespace tbs1)
     )
;

-- Dropping parent partition will drop child table partition(s), and corresponding index partitions
alter table orders drop partition orders_y2011 update global indexes;