reference partition

/****
Purpose:

Demo Oracle reference partition, 3 levels parent child relationships, partition key is not primary key or foreign key.

@C:\z00\dev\project\migration\reference\ref_partition.sql

Modification History:
Date         Who          What
12-Mar-2015: Charlie(Yi): Create the file,

****/

drop table order_item_color cascade constraint purge;
drop table order_line_items cascade constraint purge;
drop table orders cascade constraint purge;

create table orders
 (
 order_no number primary key,
 order_date date,
 data varchar2(30)
 )
enable row movement
partition by range (order_date)
(
 partition part_2014 values less than (to_date('01-01-2015','dd-mm-yyyy')),
 partition part_2015 values less than (to_date('01-01-2016','dd-mm-yyyy'))
);

insert into orders values ( 1, to_date( '01-jun-2014', 'dd-mon-yyyy' ), 'xxx' );
insert into orders values ( 2, to_date( '01-jun-2015', 'dd-mon-yyyy' ), 'xxx' );

drop table order_line_items cascade constraint purge;
create table order_line_items
(
order_no number 
 constraint order_line_items_nn1 not null,
line_no number,
data varchar2(30),  
constraint c1_pk primary key(line_no),
constraint c1_fk_p foreign key(order_no) references orders
)
enable row movement
 partition by reference(c1_fk_p)
;

insert into order_line_items values ( 1, 1, 'zzz' );
insert into order_line_items values ( 1, 2, 'zzz' );

insert into order_line_items values ( 2, 3, 'zzz' );
insert into order_line_items values ( 2, 4, 'zzz' );

select * from order_line_items partition(PART_2014);
/*
  ORDER_NO    LINE_NO DATA
---------- ---------- -----------------
         1          1 zzz
         1          2 zzz
*/

column table_name format a18
column partition_name format a15

select table_name, partition_name
from user_tab_partitions
where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS', 'ORDER_ITEM_COLOR' )
order by table_name, partition_name;

drop table order_item_color cascade constraint purge;
create table order_item_color
(
line_no number
 constraint order_line_size_nn1 not null,
color_no number,
data varchar2(30),
constraint order_item_color_pk primary key(color_no),
constraint order_item_color_fk foreign key(line_no) references order_line_items
)
enable row movement
 partition by reference(order_item_color_fk)
;

insert into order_item_color values ( 1, 1, 'y' );
insert into order_item_color values ( 2, 2, 'y' );
insert into order_item_color values ( 3, 3, 'yy' );
insert into order_item_color values ( 4, 4, 'yy' );
commit;

--alter table orders drop partition part_2014 update global indexes;

alter table orders add partition
part_2016 values less than
(to_date( '01-01-2017', 'dd-mm-yyyy' ));

select table_name, partition_name
from user_tab_partitions
where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS', 'ORDER_ITEM_COLOR' )
order by table_name, partition_name;

/*
TABLE_NAME         PARTITION_NAME
------------------ ---------------
ORDERS             PART_2014
ORDERS             PART_2015
ORDERS             PART_2016
ORDER_ITEM_COLOR   PART_2014
ORDER_ITEM_COLOR   PART_2015
ORDER_ITEM_COLOR   SYS_P877
ORDER_LINE_ITEMS   PART_2014
ORDER_LINE_ITEMS   PART_2015
ORDER_LINE_ITEMS   PART_2016
*/

select * from order_item_color partition(PART_2014);
/*
   LINE_NO   COLOR_NO DATA
---------- ---------- ------
         1          1 y
         2          2 y
*/

select * from order_item_color partition(PART_2015);
/*
   LINE_NO   COLOR_NO DATA
---------- ---------- ------
         3          3 yy
         4          4 yy
*/