About Oracle 11g REF Partitioning

By: Richard Niemiec


Oracle REF partitioning is a new partitioning feature with Oracle Database 11g. REF partitioning is the ability to partition a table based on the foreign key parent-child relationship. In REF partitioning, the partitioning key of the child table is inherited from the parent table. REF partitioning also duplicates all partition maintenance operations that change the logical shape of the parent table on the child table. REF partitioning also improves performance for joins between the parent and child table by enabling partition-wise joins. REF partitioning cannot be used when the parent table is partitioned using interval partitioning or virtual column-based partitioning.

The following message will appear when you attempt to create the partitioned table with REF partitioning:

 

0067_001

 

 

Another oddity found in this test was that if you create a partitioned index on the parent table with parallel, you could not create the child table with REF partitioning as it caused an ORA-0600. If you create the index without parallel, the child table with REF partitioning is created. To get around this issue, you can create the partitioned index on the parent table after creating the child table with the REF partitioning.

The following example shows how to do this. Note the PARTITION BY REFERENCE in the second table creation (which references the primary key in the first table).

 

0068_001

 

0067_002

0069_001

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Leave a Reply