Database Reference
In-Depth Information
Now, if we were to drop the ORDER_LINE_ITEMS partition containing 2014 data, you know and I know that
the corresponding ORDERS partition for 2014 could be dropped as well, without violating the referential integrity
constraint. You and I know it, but the database is not aware of that fact:
EODA@ORA12CR1> alter table order_line_items drop partition part_2014;
Table altered.
EODA@ORA12CR1> alter table orders drop partition part_2014;
alter table orders drop partition part_2014
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
So, not only is the approach of denormalizing the data cumbersome, resource intensive, and potentially
damaging to our data integrity, it prevents us from doing something we frequently need to do when administering
partitioned tables: purging old information.
Enter reference partitioning. With reference partitioning, a child table will inherit the partitioning scheme of its
parent table without having to denormalize the partitioning key and it allows the database to understand that the
child table is equi-partitioned with the parent table. That is, we'll be able to drop or truncate the parent table partition
when we truncate or drop the corresponding child table partition.
The simple syntax to re-implement our previous example could be as follows. We'll reuse the existing parent
table ORDERS and just truncate that table:
EODA@ORA12CR1> drop table order_line_items cascade constraints;
Table dropped.
EODA@ORA12CR1> truncate table orders;
Table truncated.
EODA@ORA12CR1> insert into orders values
2 ( 1, to_date( '01-jun-2014', 'dd-mon-yyyy' ), 'xxx' );
1 row created.
EODA@ORA12CR1> insert into orders values
2 ( 2, to_date( '01-jun-2015', 'dd-mon-yyyy' ), 'xxx' );
1 row created.
And create a new child table:
EODA@ORA12CR1> create table order_line_items
2 (
3 order# number,
4 line# number,
5 data varchar2(30),
6 constraint c1_pk primary key(order#,line#),
7 constraint c1_fk_p foreign key(order#) references orders
8 )
9 enable row movement
10 partition by reference(c1_fk_p)
11 /
Table created.
EODA@ORA12CR1> insert into order_line_items values ( 1, 1, 'zzz' );
1 row created.
 
Search WWH ::




Custom Search