Oracle Partitioning Types - V (Reference Partition)

Reference Partitioning:

As the name suggests, this has got to do with parent child relations between tables or rather primary key and foreign key relations between two tables. The table is partitioned by reference when the partition key value in a foreign key constraint of that table. The table with the primary key should be partition table, single level or composite partitioned. Fo example we will first use a single level partition table.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_pk PRIMARY KEY(DATA_id))
PARTITION BY RANGE (load_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.

Now we will create a child table with for our DATA_LOAD table.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6),
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
) PARTITION BY REFERENCE(data_load_fk)
*
ERROR at line 8:
ORA-14652: reference partitioning foreign key is not supported

SQL> !oerr ora 14652
14652, 00000, "reference partitioning foreign key is not supported"
// *Cause: The specified partitioning foreign key was not supported
// for reference-partitioned tables. All columns of the
// partitioning foreign key must be constrained NOT NULL with
// enabled, validated, and not deferrable constraints. Furthermore,
// a virtual column cannot be part of the partitioning foreign key.
//* Action: Correct the statement to specify a supported
// partitioning foreign key.

I realsized that the referencing column should be not null as its referencing a primary key column. The error can we misleading.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6) NOT NULL,
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
Table created.

Lets check the partitions for this referenced partition table.

SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col HIGH_VALUE format a80
SQL> set lines 300
SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_02_2009 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_03_2009 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_04_2009 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD_FK NO DATA_01_2009
DATA_LOAD_FK NO DATA_02_2009
DATA_LOAD_FK NO DATA_03_2009
DATA_LOAD_FK NO DATA_04_2009

So we can see that four partitions were created with the same name as the primary table. And no high value is available as the data will be stored on the basis on the data stored in the primary table.
Lets test that also, by adding a row.

SQL> insert into DATA_LOAD values (1,1,sysdate-90,1,'DELHI');
1 row created.

SQL> commit;
Commit complete.

SQL> insert into DATA_LOAD_FK values (1,'TEST',sysdate-90,1,'DELHI');
1 row created.

SQL> commit;
Commit complete.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> analyze table DATA_LOAD_FK compute statistics;
Table analyzed.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME NUM_ROWS
--------------- --- --------------- ----------
DATA_LOAD NO DATA_01_2009 0
DATA_LOAD NO DATA_02_2009 1
DATA_LOAD NO DATA_03_2009 0
DATA_LOAD NO DATA_04_2009 0

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';
TABLE_NAME COM PARTITION_NAME NUM_ROWS
--------------- --- --------------- ----------
DATA_LOAD_FK NO DATA_01_2009 0
DATA_LOAD_FK NO DATA_02_2009 1
DATA_LOAD_FK NO DATA_03_2009 0
DATA_LOAD_FK NO DATA_04_2009 0

So we can see that the partition in which the row goes in the reference partitioned table is the same as the primary table.

Now let us see the affect of composite partition table as the primary table.

SQL> drop table DATA_LOAD_FK;
Table dropped.

SQL> drop table DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_pk PRIMARY KEY(DATA_id)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6) NOT NULL,
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
Table created.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD YES DATA_01_2009 3
DATA_LOAD YES DATA_02_2009 3
DATA_LOAD YES DATA_03_2009 3
DATA_LOAD YES DATA_04_2009 3

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD_FK NO DATA_03_2009_2 0
DATA_LOAD_FK NO DATA_03_2009_3 0
DATA_LOAD_FK NO DATA_04_2009_1 0
DATA_LOAD_FK NO DATA_04_2009_2 0
DATA_LOAD_FK NO DATA_01_2009_1 0
DATA_LOAD_FK NO DATA_01_2009_2 0
DATA_LOAD_FK NO DATA_01_2009_3 0
DATA_LOAD_FK NO DATA_02_2009_1 0
DATA_LOAD_FK NO DATA_02_2009_2 0
DATA_LOAD_FK NO DATA_02_2009_3 0
DATA_LOAD_FK NO DATA_03_2009_1 0

TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD_FK NO DATA_04_2009_3 0
12 rows selected.

So you can see that the subpartitions have become individual partitions in the reference partition table.

Partitions limits cannot be specified in a reference partition table.

The partitions take the same name as the parent table partitions.

Part I
Part II
Part III
Part IV
Part VI
Part VII