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;
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)
;
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_CHILD_TR1 REFERENCE FK_TR1_TI1
TABLE_PARENT_TI1 HASH
TABLE_TI1 RANGE
TABLE_TI2 RANGE
TABLE_TR1 RANGE
TABLE_TR2 RANGE
8 TABLE_PARENT_TI1
3 TABLE_TI2
4 TABLE_TR2
3 TABLE_TR1
8 TABLE_CHILD_TR1
4 TABLE_TI1