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
*/
