Partition Wise Joins III – REF Partitioning

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.

before_ref_part_pwj

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.

with_ref_part_pwj

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!

Comments:

The only downside is that you can't mix partitioning by ref with interval partitioning. Maybe, we might get it in 12g.... P

Posted by PenFold on August 02, 2010 at 06:05 PM PDT #

Maybe in 12 :-) And yes, this is something we are aware of. JP

Posted by jean-pierre.dijcks on August 03, 2010 at 02:48 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today