What I learned today was - a new feature it is Reference Partitioning in Oracle 11g.
Reference partitioning is a new partitioning option in Oracle 11g that allows the partitioning of two related tables to be based on a referential constraint. In other words, when there is a parent-child relationship between two tables, the parent table can be defined with its partitions. Subsequently, the child table can be equipartitioned by defining the child table to inherit the partitioning key from the parent table, without the need to duplicate the partition key columns.
scott@11G> create table t(
2 x number,
3 y number,
4 z date ,
5 constraint t_pk primary key (x)
6 )
7 partition by range(z)
8 (
9 partition p1 values less than (to_date('01/01/2006','mm/dd/yyyy')),
10 partition p2 values less than (to_date('02/01/2006','mm/dd/yyyy')),
11 partition p3 values less than (to_date('03/01/2006','mm/dd/yyyy')),
12 partition p4 values less than (to_date('04/01/2006','mm/dd/yyyy'))
13 );
Table created.
scott@11G> create table t1
2 (
3 a_c number,
4 b number,
5 constraint t1_fk foreign key(a_c) references t(x)
6 )partition by reference(t1_fk);
)partition by reference(t1_fk)
*
ERROR at line 6:
ORA-14652: reference partitioning foreign key is not supported
Referential constraint on child table must be defined on a NOT NULL parent column and a virtual column cannot be part of the partitioning foreign key. Violating any of these limitations will cause an error such as ORA-14652
scott@11G> create table t1
2 (
3 a_c number NOT NULL,
4 b number,
5 constraint t1_fk foreign key(a_c) references t(x)
6 )partition by reference(t1_fk);
Table created.
scott@11G> SELECT table_name, partition_name, partition_position
2 FROM user_tab_partitions
3 WHERE table_name IN('T','T1')
4 ORDER BY table_name, partition_name
5 /
TABLE_NAME PARTITION_NAME PARTITION_POSITION
-------------------- ------------------------ ------------------
T P1 1
T P2 2
T P3 3
T P4 4
T1 P1 1
T1 P2 2
T1 P3 3
T1 P4 4
8 rows selected.
Are there any caveats to this method of partitioning? I would think that row movement would need to be turned on...and what about deadlocks.
ReplyDelete@Anonymous (Mark):
ReplyDeleteYou are correct, Row migration on parent table may lock child tables which will produce serious deadlock on transactions.
Here is the script for demonstration.
drop table child_tab purge;
drop table parent_tab purge;
create table parent_tab
(
col1 number,
col2 number,
constraint parent_tab_pk primary key(col1)
)
enable row movement
partition by list(col2)
(
partition p1 values (1),
partition p2 values (2),
partition p3 values (3),
partition p4 values (4),
partition p5 values (default)
);
create table child_tab
(
col1 number not null,
col2 number,
constraint child_tab_fk foreign key(col1)
references parent_tab
)
enable row movement
partition by reference(child_tab_fk);
insert into parent_tab(col1,col2)
select level,level
from dual
connect by level <=5;
insert into child_tab(col1,col2)
select level,level
from dual
connect by level <=5;
commit;
--session 1
update parent_tab
set col2 = col2 + 1
where col1 = 1;
--session 2
update child_tab
set col2 = col2 + 1
where col1 = 2;
--session 1 <<= this will be blocked now.
update parent_tab
set col2 = col2 + 1
where col1 = 2;
-- session 2 <<= this will produce ORA-00060: deadlock on Session-1
update child_tab
set col2 = col2 + 1
where col1 = 1;
Thanks RAJESH,
ReplyDeleteYour solution has resolved my issue.