Mastering Oracle 11g Reference Partition: Step‑by‑Step Experiments and Insights

Experiment Environment

The tests were performed on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 (64‑bit).

Creating the Master Table

SQL> create table t_master (
  object_id number,
  owner varchar2(100),
  object_name varchar2(100),
  object_type varchar2(100)
)
partition by list(owner) -- List partitioning
(
  partition p0 values ('PUBLIC'),
  partition p1 values ('SYS'),
  partition p3 values (default)
);

Primary key added:

SQL> alter table t_master add constraint pk_t_master primary key (object_id);

Creating the Detail Table with Reference Partition

Initially the attempt failed because the foreign‑key column was nullable:

SQL> create table t_detail (
  object_id number,
  master_id number,
  obj_comment varchar2(100),
  obj_type varchar2(100),
  constraint fk_mas_det foreign key (master_id) references t_master(object_id)
) partition by reference(fk_mas_det);
ORA-14652: reference partitioning foreign key is not supported

The error indicates that all columns of the partitioning foreign key must be NOT NULL, enabled, validated, and not deferrable. The fix is to declare master_id NOT NULL:

SQL> create table t_detail (
  object_id number,
  master_id number NOT NULL,
  obj_comment varchar2(100),
  obj_type varchar2(100),
  constraint fk_mas_det foreign key (master_id) references t_master(object_id)
) partition by reference(fk_mas_det);
Table created

Inspecting Partition Metadata

Querying dba_tab_partitions shows that the master table has three list partitions (P0, P1, P3) and the detail table inherits the same three partitions, even though its high_value column is empty because the partition key is derived from the foreign key.

SQL> select partition_name, high_value, partition_position
from dba_tab_partitions
where table_owner='SYS' and table_name='T_MASTER';

PARTITION_NAME  HIGH_VALUE  PARTITION_POSITION
P0              'PUBLIC'    1
P1              'SYS'       2
P3              default     3

SQL> select partition_name, high_value, partition_position
from dba_tab_partitions
where table_owner='SYS' and table_name='T_DETAIL';

PARTITION_NAME  HIGH_VALUE  PARTITION_POSITION
P0              (null)      1
P1              (null)      2
P3              (null)      3

Segment Information

All partitions exist as separate table partitions on disk:

SQL> select segment_name, partition_name, segment_type
from dba_segments
where owner='SYS' and segment_name in ('T_MASTER','T_DETAIL');

SEGMENT_NAME  PARTITION_NAME  SEGMENT_TYPE
T_MASTER      P0              TABLE PARTITION
T_MASTER      P1              TABLE PARTITION
T_MASTER      P3              TABLE PARTITION
T_DETAIL      P0              TABLE PARTITION
T_DETAIL      P1              TABLE PARTITION
T_DETAIL      P3              TABLE PARTITION

Data Insertion and Distribution Check

Rows from dba_objects were inserted into the master table and then into the detail table. After gathering statistics, the row counts per partition matched exactly, confirming that each master‑partition record’s child rows reside in the corresponding detail partition.

SQL> select table_name, partition_name, high_value, num_rows
from dba_tab_partitions
where table_owner='SYS' and table_name='T_MASTER';

T_MASTER  P0  'PUBLIC'  33996
T_MASTER  P1  'SYS'     37817
T_MASTER  P3  default   48548

SQL> select table_name, partition_name, high_value, num_rows
from dba_tab_partitions
where table_owner='SYS' and table_name='T_DETAIL';

T_DETAIL  P0  (null)  33996
T_DETAIL  P1  (null)  37817
T_DETAIL  P3  (null)  48548

After inserting additional rows, the detail table partitions grew proportionally, further confirming the automatic alignment.

Practical Implications

Reference Partition simplifies management by automatically placing child rows in the same partition as their parent, eliminating the need for explicit partition keys on the child table. This yields benefits in query performance (partition pruning, local indexes) and administrative tasks (partition maintenance, archiving).

Future articles will explore these performance and management advantages in depth.