29 | 03 | 2024
Latest Articles
Popular Articles

Database Design

Reference partitioned tables

User Rating:  / 0
PoorBest 

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