Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Reference Partitioning tips

Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

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.


------------------------------ --------- ---------------------------
PARENT_EMP                     LIST

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.  


This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.