| Oracle: create a partition by reference (partition by reference(fk)) on non referenced table [message #681800] |
Tue, 01 September 2020 03:10  |
 |
yarfaoui
Messages: 1
Registered: September 2020
|
Junior Member |
|
|
We have realized that we need to use Oracle (12 c) interval partitioning.We have an hierarchical entity model with lots of @OneToMany relationships.We want to use "partitioning by range" (day,month... ) on the "parent/root" entity (A) and "partition by reference" on all child entities (B). From the Oracle documentation: "Reference partitioning allows the partitioning of two tables related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints." The problem is that child entities (B) can refer to other entities ( C) that they don't have any link with the "parent/root" entity (A). I can create partitions on A and on B but when I want to drop the partition on A ( partition on B on cascade), I get an error integrity error and it fails. it works only if I delete all records on C and B and then partition them. I don't want to do that as it's not efficient and slow compared to dropping partitions directly
Please is there a way to create a partition on table C based on A(creation_date) without adding any foreign constraint between A and C?
Small example to illustrate the case
A - parent entity
B - child entity to A
C - child entity to B
create table
A (
id number primary key,
creation_date date
)
partition by range (creation_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
partition p1 values less than (to_date('20180501','yyyymmdd')),
);
create table
B (
id number primary key,
value varchar2(5),
a_id number not null,
constraint fk_ba foreign key (a_id) references A
)
partition by reference(fk_ba);
create table
C (
id number primary key,
code varchar2(5),
b_id number not null,
constraint fk_cb foreign key (b_id) references B
)
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
