Database Design
Reference partitioned tables
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 22:51
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Thursday, 18 March 2010 22:51
-
Written by Guy Lambregts
-
Hits: 3547
Reference Partitioned tables
Interval partitioning is a new feature of 11G but there is more.
In almost any relation database we have a child parent relationship between tables. At the database design level this relationship is often enforced by foreign keys. Queries will very often query for data from both child and parent tables.
If our parent table contains a huge amount of data and is partitioned it could be nice our child table is partitioned and with the partion key the foreign key pointing to the parent table.
This is nice for optimal partition wise joins ( more about partition wise joins elsewhere and later )
It does not take a hero to implement referential partitioning
The parent table is hash partitioned
create table TABLE_PARENT_TI1 (
TI1_ID NUMBER(18) not null,
TI1_TRANSACTIONDATE DATE default SYSDATE not null,
TI1_TRANSACTIONVALUE number(10,2) not null,
TI1_ISINCODE varchar2(100) )
partition by hash (TI1_ID) partitions 8;
alter table TABLE_PARENT_TI1 add constraint PK_PARENT_TI1 primary key (TI1_ID);
create sequence SEQ_PARENT_TI1 cache 1000;
The child table is referential partitioned
create table TABLE_CHILD_TR1 (
TR1_ID NUMBER(18) not null,
TR1_TI1 NUMBER(18) not null,
TR1_TRANSACTIONDATE DATE default SYSDATE not null,
TR1_TRANSACTIONVALUE number(10,2) not null,
TR1_ISINCODE varchar2(100) not null,
TR1_AMOUNT number(10,2) not null,
TR1_TYPE varchar2(2) not null,
TR1_PERS varchar2(100) not null,
TR1_PRICE number(10,2) not null,
constraint FK_TR1_TI1 foreign key ( TR1_TI1 ) references TABLE_PARENT_TI1 )
partition by reference (FK_TR1_TI1)
;
alter table TABLE_CHILD_TR1 add constraint PK_CHILD_TR1 primary key (TR1_ID);
create sequence SEQ_CHILD_TR1 cache 1000;
With the below listed selects we list TABLE_CHILD_TR1 is reference partitioned on the foreign key FK_TR1_TI1 and that we have automatically an identical number of partitions as the parent table ( 8 )
SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables;
TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME
TABLE_CHILD_TR1 REFERENCE FK_TR1_TI1
TABLE_PARENT_TI1 HASH
TABLE_TI1 RANGE
TABLE_TI2 RANGE
TABLE_TR1 RANGE
TABLE_TR2 RANGE
SQL> select count(*),table_name from user_tab_partitions group by table_name;
COUNT(*) TABLE_NAME
8 TABLE_PARENT_TI1
3 TABLE_TI2
4 TABLE_TR2
3 TABLE_TR1
8 TABLE_CHILD_TR1
4 TABLE_TI1