Donald K. Burleson Advanced Oracle Tips and Tricks for the Oracle DBA

Reference Partitioning in Oracle 11g

Oracle 11g provides an additional new type of partitioning called reference partitioning which allows for partitioning by using the parent table referenced by a foreign key constraint. It allows for use of existing parent-child relationship based partitioning enforced by active primary key and foreign key constraints. The partition by reference clause is used to create reference partitions as shown in the following example. First, we will need to create a table that is partitioned to server as the parent table which will be referenced by the second table that 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  );

Table created.

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  /
 

Table created.

Now that we have our reference partition created on the child table, we can query both tables to determine the partition information from the user_part_tables view.

SQL> select table_name, partitioning_type, ref_ptn_constraint_name
  2  from user_part_tables
  3  where table_name='PARENT_EMP'
  4  or
  5  table_name='REFERENCE_EMP';

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- ------------------------------
PARENT_EMP                     LIST
REFERENCE_EMP                  REFERENCE FK_EMPNO

Here we see that the REFERENCE_EMP child table has been reference partitioned using the foreign key constraint FK_EMPNO. Next we can view the high values for the partitions in both tables 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'

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
REFERENCE_EMP                  P_JOB_DBA
REFERENCE_EMP                  P_JOB_MGR
REFERENCE_EMP                  P_JOB_VP

6 rows selected.

Reference partitioning is useful in that partition operations against the parent table are duplicated to the child table thus reducing errors when manually performing maintenance against both tables. Before Oracle 11g, you would have to manually allocate storage for the partition key on both the parent and child table. Now with Oracle 11g, less storage is required and easier maintenance is provided.

New Feature for Oracle 11g: Active Data Guard

Oracle 11g introduces several new enhancements to improve the operation and maintenance of complex Data Guard standby environments for improved high availability. The many new features with Oracle 11g for Data Guard include the following:

• Real time query standby database

• Redo log compression

• Logical standby enhancements

• Data Guard Broker enhancements

• Fast start failover improvements

• Integration for RMAN with Data Guard

• Snapshot standby database

Further details on the steps for how to configure and setup a physical standby and logical standby database using the new features for Oracle 11g with Data Guard are available in the Oracle 11g Data Guard Concepts and Administration reference guide available from the Oracle OTN site located online at http://otn.oracle.com.

Copyright © 1996 -  2011 by Burleson Enterprises. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.