Thursday, March 12, 2015

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

Tuesday, March 10, 2015

Reading notes - Developing Successful Oracle Applications

Here we go,

  • Knowing what’s out there in Oracle. 
  • Solving problem simply.  (Don’t reinvent the wheel)
  • Layered programming.   (Database API)
 
“A layer of abstraction and indirection is another example of defensive programming to allow for portability and code scalability. it is to layer our access to the database when necessary.”
 
“No matter what database we are using, we paid a lot for these technologies,
isn't it in our best interest to exploit it to the fullest extent possible, squeezing every last bit of functionality we can out of that product ? ”