Reference partitioning is useful in the following scenarios:
If you have denormalized, or would denormalize, a column from a master table into a child table to get partition pruning benefits on both tables.
For example, your orders table stores the order_date, but the order_items table, which stores one or more items for each order, does not. To get good performance for historical analysis of orders data, you would traditionally duplicate the order_date column in the order_items table to use partition pruning on the order_items table.
You should consider reference partitioning in such a scenario and avoid having to duplicate the order_date column. Queries that join both tables and use a predicate on order_date automatically benefit from partition pruning on both tables.
If two large tables are joined frequently, then the tables are not partitioned on the join key, but you want to take advantage of partition-wise joins.
Reference partitioning implicitly enables full partition-wise joins.
If data in multiple tables has a related life cycle, then reference partitioning can provide significant manageability benefits.
Partition management operations against the master table are automatically cascaded to its descendents. For example, when you add a partition to the master table, that addition is automatically propagated to all its descendents.
To use reference partitioning, you must enable and enforce the foreign key relationship between the master table and the reference table in place. You can cascade reference-partitioned tables.
The primary key-foreign key relationship must be enabled all the time and cannot be disabled. Also the relationship cannot be declared as deferred. These are mandatory requirements because the enabled primary key-foreign relationship is required to determine the data placement for the child tables.