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 reference partitions.
Subsequently, the child table can be equi-partitioned by
defining the child table to inherit the partitioning key
from the parent table, without the need to duplicate the
partition key columns.
This logical reference partitioning is
achieved by the inheritance of the parent's partitioning
scheme through a referential constraint in the child table.
The reference partitioning feature enhances the manageability of the child table
because all partition maintenance operations on the parent
table automatically cascade to the child table.
Additionally, reference partitioning relieves the DBA of the
responsibility of maintaining duplicate columns, and
partitions in the child table, in order to achieve the
same objective without reference partitioning.
Using Reference Partitioning
In any relational database, there are a
countless number of situations where parent-child
relationships exist. Consider the abundance of parent-child
table relationships in Oracle schemas that support online
transaction processing or data warehousing.
First, we create a table that is partitioned to the server as the
parent table. This table will be referenced by the second table
which is to be reference partitioned.
SQL> create table parent_emp(
2 empno number primary key,
3 job varchar2(20),
4 sal number(7,2),
5 deptno number(2)
6 )
7
partition by list(job)
8 ( partition p_job_dba values ('DBA'),
9 partition p_job_mgr values ('MGR'),
10 partition p_job_vp values ('VP')
11 );
SQL> create table reference_emp
2 (
3 ename varchar2(10),
4 emp_id number primary key,
5 empno not null,
6 constraint fk_empno foreign key(empno)
7 references parent_emp(empno)
8 )
9
partition by reference (fk_empno)
10 /
The reference partition is now created on the child table and
both tables can be queried to determine the partition information
from the user_part_tables.
select
table_name,
partitioning_type,
ref_ptn_constraint_name
from
user_part_tables
where
table_name='PARENT_EMP'
or
table_name='REFERENCE_EMP';
TABLE_NAME
PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ ---------
---------------------------
PARENT_EMP LIST
REFERENCE_EMP REFERENCE FK_EMPNO
The reference_empchild
table has been reference partitioned using the foreign key
constraint fk_empno. Next
we query the high values for the reference partitions in both
the parent table and the reference partition with
the following query:
SQL> select table_name,
partition_name, high_value
2 from user_tab_partitions
3 where table_name='PARENT_EMP'
4 or
5 table_name='REFERENCE_EMP'
6 /
TABLE_NAME
PARTITION_NAME HIGH_VALUE
------------------------------ -----------------------------------
PARENT_EMP P_JOB_DBA 'DBA'
PARENT_EMP P_JOB_MGR 'MGR'
PARENT_EMP P_JOB_VP 'VP'
REFERENCE_EMP P_JOB_DBA
REFERENCE_EMP P_JOB_MGR
REFERENCE_EMP P_JOB_VP
Limitations of reference partitioning
cannot be used when the parent table uses interval
partitioning. 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: reference partitioning foreign key is not
supported
Reference partitioning improves and
simplifies the partitioning of tables that have a
parent-child relationship. Reference partitioning also allows the DBA to
maintain both sets of partitions by only managing partitions
on the parent table.
The ability to partition by reference removes the necessity for
managing the partitions on the child table. Similarly,
reference partitioning also eliminates the need to include
unnecessary duplicate columns from the parent table to
enable equi-partitioning of the child table.
and include the URL for the page.
