Database Reference
In-Depth Information
'201 COUNT(*)
---- ----------
2015 0
2016 1
An update against the parent was cascaded down to the child table and caused the child table to move a row
(or rows as needed).
To summarize, reference partitioning removes the need to denormalize data when partitioning parent and child
tables. Furthermore, when dropping a parent partition, it will automatically drop the referenced child partition. These
features are very useful in data warehousing environments.
Interval Reference Partitioning
Prior to Oracle 12
c
, the combination of interval and reference partitioning was not supported. For example,
in Oracle 11
g
if you create an interval range partitioned parent table, as follows:
EODA@ORA11GR2> create table orders
2 (order# number primary key,
3 order_date timestamp,
4 data varchar2(30))
5 PARTITION BY RANGE (order_date)
6 INTERVAL (numtoyminterval(1,'year'))
7 (PARTITION part_2014 VALUES LESS THAN (to_date('01-01-2015','dd-mm-yyyy')) ,
8 PARTITION part_2015 VALUES LESS THAN (to_date('01-01-2016','dd-mm-yyyy')));
Table created.
And then attempt to create a reference partitioned child table, an error is thrown, as follows:
EODA@ORA11GR2> create table order_line_items
2 ( order# number,
3 line# number,
4 data varchar2(30),
5 constraint c1_pk primary key(order#,line#),
6 constraint c1_fk_p foreign key(order#) references orders)
7 partition by reference(c1_fk_p);
create table order_line_items
*
ERROR at line 1:
ORA-14659: Partitioning method of the parent table is not supported
That is no longer the case starting with Oracle 12
c
, where you can combine interval and reference partitioning.
Running the prior code in an Oracle 12
c
database, the creation of the child table succeeds:
EODA@ORA12CR1> create table order_line_items
2 ( order# number,
3 line# number,
4 data varchar2(30),
5 constraint c1_pk primary key(order#,line#),
6 constraint c1_fk_p foreign key(order#) references orders)
7 partition by reference(c1_fk_p);
Table created.
