Partition Wise Joins III – REF Partitioning
By Jean-Pierre Dijcks on Aug 02, 2010
Quick and short post as a follow up to the ODTUG session I did on Partitioning. With Database 11g we introduced REF partitioning, which allows us to do PWJ's in a master-detail scenario. Which of course is very cool in any 3NF schema where we have lots of these examples.
The above is a simple and typical example, where an orders table has child records in a lineitem table. Before REF partitioning we needed to store the ORDER_DATE column in both the parent (where it belongs) and the child table. That latter aspect meant double maintenance and data duplication. Not good, but at least you got that child table partitioned on ORDER_DATE.
Now that we implemented REF partitioning, we do not need that ORDER_DATE information to be stored in the lineitem table, because the partitioning is inherited via the reference from the parent table leveraging the PK-FK constraint in place. No more double maintenance, no more duplication of data, but nicely PWJ's.
The PWJ comes from the fact that all lineitems are partitioned on the owning parent's order_date, and each of the children lives in that "same" partition. All in all a nice and simple way to get a 3NF parent-child construct to product PWJ's!